Coach, an opening statement?ĬOACH IZZO: We're excited. That's identical to the secondary key! So, the optimizer may as well flip a coin to decide between them.Īnyway, it says "Using index" that about as efficient as it gets.THE MODERATOR: We're joined by Michigan State head coach Tom Izzo and student-athletes Cassius Winston, Matt McQuaid and Nick Ward. Another guess: You have PRIMARY KEY(person, field) and INDEX(person, field, value)? And you are using InnoDB? The PK includes all columns and is sorted by (person, field). If the WHERE clause matches more than ~20%, it will choose to do a table scan because it is likely to be faster. Why does MySql generally decline to use the PersonID/FieldID/Value index? Is there a better index I could use? I assumed I needed the PersonID in the index to help with the join. If you have a new enough version of MySQL, do EXPLAIN FORMAT=JSON SELECT.
EXPLAIN likes to put those flags on the first line, even though they might apply to some other line. That is beyond the scope of this forum, other than to point you at those two blogs.
#KEYKEY ALWAYS SLAY INSTAGRAM SOFTWARE#
Software solution involves a major rework of the schema and application code. Of course, neither hardware solution scales well. And/or switch from spinning drives to SSDs.
Your best choice may be to keep buying RAM so that everything can be cached. Further discussion and a possible (but unlikely) workaround: Strike 2: GUIDs (and UUIDs, MD5s, etc) suck as KEYs because they are so random. Further discussion and possible workarounds: Strike 1: EAV (key-value) schema sucks when the tables get large. Sorry, but you have designed yourself into a corner. Why does MySql generally decline to use the (PersonID, FieldID, Value) index? Is there a better index I could use? I assumed I needed the PersonID in the index to help with the join.Įdit Here is the relevant portion of the explain: id select_type table type key key_len ref rows Extraġ SIMPLE p ref deleted_person 67 const,const 3476105 Using where Using index Using temporary Using filesortġ SIMPLE pd ref person_field_val 62 p.PersonID 10 Using indexĮdit 2 Forgot the GROUP BY in the query above. Why does MySql need to sort the Person table before joining to PersonData? On the explain I always get a “Using where Using index Using temporary Using filesort” on the Person table. (Actual results of explain vary based on FieldID.) I also have an index on Person on (GroupID, DeletedDate). MySql generally chooses not to use it when I run explain.
I created an index on PersonData on (PersonID, FieldID, Value) in that order. It would be acceptable to get it down to a few minutes. The group in question has 5 million records in table Person and an order of magnitude beyond that in PersonData. Performance is fine for small groups but degrades rapidly. WHERE p.DeletedDate IS NULL AND p.GroupID=‘cAPHSWovx9d2yaN’ INNER JOIN PersonData pd ON p.PersonID=cd.PersonID Here is the query: SELECT pd.Value, count(*) I am trying to run a histogram-type query to discover the range of values for a given FieldID together with a count of the times each value occurs. The fields are custom-created hence the schema.
0 kommentar(er)
