mysql range index

Question

Hi everybody, 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

Answer 1

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.

Answer 2

maybe that's the problem :) in table are only some data for testing, so there is only about 30 rows.