Mysql range index
I have a very simple choice:
SELECT * FROM table
WHERE column1 IN (5, 20, 30);
the index is set on column1, after explaining the query, the index is used, everything looks ok.
but if the range is more than three values, for example:
SELECT * FROM table
WHERE column1 IN (5, 20, 30, 40);
no index is used and selection is made across all records. Am I doing something wrong? thank
0
source to share
1 answer
How many rows in MySql appear to be in a table?
Mysql often (usually correctly!) Assumes that it will perform sequential string scans faster rather than interfering with more complex index access.
It varies from RDBMS to RDBMS, but the trade-off point is around 30% of the rows.
IE. If the optimizer expects more than 30% of the rows to be fetched, it will scan the entire table sequentially, as this is usually faster than doing a lot of direct index accesses.
+2
source to share