Re: Why is wrong index being selected
Hi Steve,
When the MySQL Optimizer decides which index to use, it looks at how many rows needs to be read from each index. If the numbers are equal for two indexes, it will choose the smallest one (in total index size).
The estimated number of rows within an index range is computed by the storage engine. In the case of InnoDB, it navigates the B-tree for the start and end values of the range, and returns an estimate based on the "distance" between the two values. Hence, the estimate is based on the constants used in the query. This can explain why you see that a different index is used when you change the value of a constant.
If the condition on the first column of an index is not equality, only the first column will be used to determine the index range. In your case, this means that all index entries with "status < 300" will have to be read from both indexes. Hence, the estimated number of rows will be about the same for both indexes, and it will choose the smallest one, which probably is index 2.
If the condition on the first column is equality, condition on second column can also be used when setting up the range for the index scan. Hence, one should think that index 1 should be picked for "status = 100" since the condition on customer reference would also be used when setting up the range. However, if there is no (or very few) rows for which status is 100 and customer reference is an empty string, the estimated number of rows will still come out the same as for the other index. My guess is that when status is 0 there are more empty customer references, so that row estimate is lower for using index 1 in that case.
Regards,
Øystein Grøvlen,
Senior Principal Software Engineer,
MySQL Group, Oracle,
Trondheim, Norway