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...
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