Rereading the documentation on the index came across a paragraph

In some cases, MySQL does not use an index, even if it is possible. Some examples of such situations are given below:

  • If the use of the index requires MySQL to pass more than 30% of the rows in this table (in such cases, viewing the table is likely to be much faster, since you will need to perform fewer search operations). It should be noted that if such a query uses LIMIT with respect only to the extracted part of rows, then MySQL will use the index anyway, since a small number of rows can be found much faster in order to return the result.

  • If the index range can contain NULL values ​​when using ORDER BY ... DESC expressions.

And if everything is very clear with the second paragraph, then the formulation of the first paragraph does not fit in my head. Trying to figure it out, I got into the original documentation and could not find a similar description at all.

Therefore, I would like to know - in what cases the current version (5.7) does not use indices when it is possible?

  • one
    Before starting the execution of the query, an execution plan is drawn up. In particular, in this respect (on the basis of the statistics collected earlier), the approximate number of rows that will be returned is calculated. And if according to the forecasts of the scheduler of these lines will be more than 30% of the entire table, then there is no sense in using the index. - rjhdby

0