Re: Mysql does not use index, even if example is trivial?

[Rick James]

There are times when it is faster to scan the entire table instead of using the index. This is because using the index requires two steps per row (one to read the index, the other being a random fetch into the data).

MySQL will look at the data you give in the particular SELECT, peek into the index(es), then decide what will be the fastest way to run the query. Different data can lead to different plan.

With only 6 rows in the table, either approach will be so fast that it does not really matter. With 6 million rows, it is quite important.

Given a large table, and the inequality you give it would select 90% of the table -- table scan, tossing the unnecessary 10% is much faster than using the index. Selecting 10% -- the index is much faster. (Rumor has it that the actual cutoff is 30%.)

If this is a MyISAM table, performing ANALYZE TABLE will have it recalculate the statistics on which it makes its query plans.

InnoDB works differently; it does some random probes (ANALYZE) into the table when you open it. These probes are imprecise, and can sometimes lead to the "wrong" index choice. (The probing algorithm was improved a few months ago.)

Bottom line: It is not the comparison operator by itself that caused the difference.