MySQL index doesn't help in IN

Question

If I write this query,

EXPLAIN SELECT * FROM `smth` WHERE d_id = 9

it checks one row instantly. But when I use IN, for example

EXPLAIN SELECT * FROM `smth` WHERE d_id IN (9, 3)

it checks all rows. What should I do instead of IN if index doesn't help there?

Answer 1

MySQL 5.1 does have a range join type that optimizes IN predicates. See http://dev.mysql.com/doc/refman/5.1/en/explain-output.html

However, the MySQL optimizer may have determined that your d_id column contains values 9 and 3 in so great a majority of rows that it decided it would be cheaper to read the whole table than to read the index and then the table.

By analogy, if you read a book and you want to find every page that has the word "the" would you go to the index at the back of the book, look up "the," and flip to each page in turn? Or would you just read the book cover to cover because almost every page is guaranteed to have that word on it?

Answer 2

It's probably because your table doesn't contain many rows. If your table contained more rows it probably would use the index. Here's an example from a table with one million rows:

EXPLAIN SELECT * FROM table1 WHERE x = 9;
1, 'SIMPLE', 'table1', 'const', 'PRIMARY', 'PRIMARY', '4', 'const', 1, ''

EXPLAIN SELECT * FROM table1 WHERE x IN ( 9, 3);
1, 'SIMPLE', 'table1', 'range', 'PRIMARY', 'PRIMARY', '4', '', 2, 'Using where'

In both cases the primary key index is used.