mysql performs full table scan even though index exists

Question

so I have this table containt 100000 rows

field1 field2 

now i Just added a new column field3 and moreover there is an index on field3

field1 field2 field3

so I added about 50 rows that contains field3 (the other rows have field3 as NULL)

so I do a select

SELECT * FROM table WHERE field3 IN (val1, val2);

an explain of that is fairly sane. It uses the index on field3 and only scans 2 rows

however as I add more values in the IN statement

SELECT * FROM table WHERE field3 IN (val1, val2, val3, val4, val5, val6, val7, val8, val9, val10);

This ends up not using the index and ends up performing a full table scan of the entire 100000+ rows.

why is mysql doing this? I know that mysql "If you need to access most of the rows, it is faster to read sequentially, because this minimizes disk seeks." from http://dev.mysql.com/doc/refman/5.1/en/mysql-indexes.html

but this can't possibly be faster than using the index to fetch those 10 values

why is mysql doing this and how can I instruct mysql to force them to use the index instead of performing full table scan...

Answer 1

Q: Why is MySQL doing this?

A: Likely MySQL's estimate of the cardinality of the index is different than you expect, and MySQL is estimating that a full table scan is a more efficient plan than using the index. There are ways to influence statistics, with both MyISAM and InnoDB. Reference: http://dev.mysql.com/doc/refman/5.5/en/myisam-index-statistics.html

Q: How can I instruct MySQL to force them to use the index?

A: You can try to influence statistics collection, so MySQL comes up with a different cardinality.

Or you can try including an index hint in the query text:

SELECT * FROM mytable FORCE INDEX myindex WHERE ...

Or, you could try re-writing the query as multiple SELECTs combined with UNION ALL set operator:

SELECT * FROM mytable WHERE field3 = val1
 UNION ALL
SELECT * FROM mytable WHERE field3 = val2
 UNION ALL
SELECT * FROM mytable WHERE field3 = val3