Ernie Souhrada has a great writeup called The Optimization That (Often) Isn’t: Index Merge Intersection. It talks about how MySQL can perform an "index merge" in order to use more than one index.
For example, if you have an OR in your WHERE clause, and each of the conditions are indexable, MySQL can perform each index lookup separately, and then do a union on the results. Similarly, if you have an AND in your WHERE clause, and again each condition is indexable, MySQL can do an intersection of the results of the two indexes. MySQL will decide whether to use this optimization based on the statistics it keeps of your table's indexes.
The problem that Ernie identified happened because the customer had an index on a column with very little variance. Almost all of the rows had the same value, and the optimizer made an incorrect assumption when evaluating indexes.
For this reason, you should be very cautious about indexing columns with very little variance. It can make sense sometimes; perhaps almost all of the rows have a 1 in that column, but you need to find the few that have a 0. This is common when you have to process new rows, and then mark them as "processed". In this case, I will often use a separate queue table; this negates the possibility of the optimizer choosing to use a very unbalanced index on the main table.
Post a Comment