MySQL Forums
Forum List  »  InnoDB

Re: why must i specify an index in query?
Posted by: Rick James
Date: July 03, 2015 09:12PM

When would the optimizer not use an index?

http://mysql.rjweb.org/doc.php/index_cookbook_mysql#flags_and_low_cardinality
says

INDEX(flag) is almost never useful if `flag` has very few values. More specifically, when you say WHERE flag = 1 and "1" occurs more than 20% of the time, such an index will be shunned. The Optimizer would prefer to scan the table instead of bouncing back and forth between the index and the data for more than 20% of the rows.

("20%" is really somewhere between 10% and 30%, depending on the phase of the moon.)

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: why must i specify an index in query?
901
July 03, 2015 09:12PM


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.