Geek Answers Handbook

MySQL index does not help in IN

If I write this request,

EXPLAIN SELECT * FROM `smth` WHERE d_id = 9

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

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

he checks all the lines. What should I do instead of IN if the index does not help there?

+5
source share
2 answers

MySQL 5.1 has a join type rangethat optimizes predicates IN. See http://dev.mysql.com/doc/refman/5.1/en/explain-output.html

However, the MySQL optimizer can determine that the column d_idcontains values ​​9 and 3 in such a large number of rows that it decided that it would be cheaper to read the whole table than to read the index and then the table.

, , "the", , "the" ? , , ?

+7

, , . , , , . :

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'

.

+1

More articles:


All Articles