MySQL Forums
Forum List  »  Performance

Re: Why is wrong index being selected
Posted by: Øystein Grøvlen
Date: February 24, 2016 03:22AM

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

Options: ReplyQuote


Subject
Views
Written By
Posted
1404
February 23, 2016 01:54PM
821
February 23, 2016 11:39PM
Re: Why is wrong index being selected
997
February 24, 2016 03:22AM
817
February 25, 2016 02:47AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.