mysql索引 范围,mysql范围索引

I have very simple select like this:

SELECT * FROM table

WHERE column1 IN (5, 20, 30);

on column1 is seted index, after explaining query is index used, all looks to be ok.

but if there are more than three values in range, like this:

SELECT * FROM table

WHERE column1 IN (5, 20, 30, 40);

index is not used and select runs thru all records. Am I doing something wrong? thanks

解决方案

How many rows does MySql think there are in the table?

Mysql often (usually correctly!) assumes it will be quicker to do a sequential scan of the rows, rather than mess around with the more complex access via an index.

It varies from DBMS to DBMS but the tradeoff point is somewhere about 30% of the rows.

IE. If the optimiser expects more than 30% of the rows to be selected it will sequentially scan the whole table as this is usually faster than doing lots of direct access via indexes.