Clever Geek Handbook

Why doesn't MySQL use indexes with WHERE IN clauses?

I am trying to get multiple records by a composite index from a table with PRIMARY KEY (a, b)

SELECT * FROM table WHERE (a, b) IN ((1,2), (2,4), (1,3))

The problem is that MySQL is not using the index even if I FORCE INDEX ( PRIMARY

).
EXPLAIN SELECT shows null possible_keys.

Why are there no possible_keys?

What is the best way to get multiple rows using a composite key:

  • using OR
  • using UNION ALL
  • using WHERE () IN ((), ())

PS Request for result is

SELECT * FROM table WHERE (a = 1 AND b = 2) OR (a = 2 AND b = 4) OR (a = 1 AND b = 3)

thank

+3


source to share


3 answers


If the query selects only fields from the index (or if the table has no other fields) using a composite WHERE ... IN

, the index will be used:

SELECT a,b FROM `table` WHERE (a, b) IN ((1,2), (2,4), (1,3))

Otherwise, it will not be used. A workaround is to use a derived query:



SELECT t.* FROM (SELECT a, b FROM `table` WHERE (a, b) IN ((1,2), (2,4), (1,3))) AS o INNER JOIN `table` AS t ON (t.a = o.a AND t.b = o.b)

EXPLAIN SELECT:

id  select_type table   type    possible_keys   key key_len ref rows    Extra
1   PRIMARY <derived2>  ALL NULL    NULL    NULL    NULL    2   
1   PRIMARY t   eq_ref  PRIMARY PRIMARY 2   o.a,o.b 1   
2   DERIVED table   index   NULL    PRIMARY 2   NULL    6   Using where; Using index
+1


source


In a strong desire to index a specific column, you think you have a new column: a_b

which basically CONCAT(a, '-', b)

and just compares this ( WHERE a_b = {$id1}-{$id2}

)?



And you can only have one PRIMARY column per table. You can not "primary index" how a

andb

0


source


Try creating a concatenated index on columns a, b.

The index doesn't have to be the primary key, and it can still help.

More details about your problem here: http://dev.mysql.com/doc/refman/5.0/en/multiple-column-indexes.html

0


source






More articles:


All Articles