Why is MySQL not using indexes with composite WHERE IN?

Question

I am trying to get several records by composite index from a table having 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 index, even if I FORCE INDEX (PRIMARY).
EXPLAIN SELECT shows null possible_keys.

Why there are no possible_keys?

What is the best way to retrieve multiple rows by composite key:

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

P.S. Query is equal by result to

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

Thanks

score 3 · Accepted Answer · answered Mar 07 '12 at 07:20

If query selects only fields from index (or if table has no other fields) by composite WHERE ... IN, 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. The workaround is to use 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

score 0 · Answer 2 · answered Mar 06 '12 at 10:37

0

In strong desire of indexing a certain column, have you considered having a new column: a_b which is basicly CONCAT(a, '-', b) and just compare that (WHERE a_b = {$id1}-{$id2})?

And you can only have one PRIMARY column per table. You can't "index primary" both a and b

answered Mar 06 '12 at 10:37

Robin Castlin

10,956
1
28
44

Using `CONCAT(a, '-', b)` is not a good option for me, as it can fail if I have `'-'` in field value. My PRIMARY KEY is multicolumn, so it contains both `a` and `b`. – vearutop Mar 06 '12 at 10:39
`CONCAT(a, '!-!', b)` - do you ever use that value? :) – Robin Castlin Mar 06 '12 at 10:48
My table contains hundreds of millions of records, I don't think indexed string column would increase performance. I'd rather go with UNION ALL for batch composite select. – vearutop Mar 06 '12 at 11:02

score 0 · Answer 3 · answered Mar 06 '12 at 12:51

0

Try to create the combined index on the columns a,b.

Index doesn't need to be primary key and it can still help a lot.

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

answered Mar 06 '12 at 12:51

Jiří Herník

2,412
1
25
26

Why is MySQL not using indexes with composite WHERE IN?

3 Answers3