MySQL: very slow query using composite key in WHERE IN clause

Question

I have a MySQL table with a composite primary key: (id1, id2). I want to get all the rows where (id1, id2) is among a list of pairs e.g. ((1,2), (2,6), (1,6)). When this list of pairs contains only one element, the index seems to be used as the query is extremely fast. When the list of pairs contains more than one element, the query is extremely slow:

mysql> SELECT id1, id2 FROM my_table WHERE (id1, id2) in ((1817279, 0));
+---------+--------+
|   id1   |   id2  |
+---------+--------+
| 1817279 |      0 |
+---------+--------+
1 row in set (0.00 sec)

mysql> SELECT id1, id2 FROM my_table WHERE (id1, id2) in ((1819781, 2));
+---------+--------+
|   id1   |   id2  |
+---------+--------+
| 1819781 |      2 |
+---------+--------+
1 row in set (0.00 sec)

mysql> SELECT id1, id2 FROM my_table WHERE (id1, id2) in ((1817279, 0), (1819781, 2));
+---------+--------+
|   id1   |   id2  |
+---------+--------+
| 1817279 |      0 |
| 1819781 |      2 |
+---------+--------+
2 rows in set (1 min 22.72 sec)

I attempted to use FORCE INDEX (PRIMARY) without success. Any idea why MySQL fails to use the index?

Here is my MySQL version: mysql Ver 14.14 Distrib 5.6.33, for debian-linux-gnu (x86_64) using EditLine wrapper

Answer 1

"Row Constructors" were not optimized until 5.7.3. Example WHERE (id1, id2) = (11, 22). From the changelog: 2013-12-03 5.7.3

Milestone 13 -- Functionality Added or Changed:

The optimizer now is able to apply the range scan access method to queries of this form:

SELECT ... FROM t1 WHERE ( col_1, col_2 ) IN (( 'a', 'b' ), ( 'c', 'd' ));

Previously, for range scans to be used it was necessary for the query to be written as:

SELECT ... FROM t1 WHERE ( col_1 = 'a' AND col_2 = 'b' ) OR ( col_1 = 'c' AND col_2 = 'd' );

For the optimizer to use a range scan, queries must satisfy these conditions:

• Only IN predicates can be used, not NOT IN.

• There may only be column references in the row constructor on the IN predicate's left hand side.

• There must be more than one row constructor on the IN predicate's right hand side.

• Row constructors on the IN predicate's right hand side must contain only runtime constants, which are either literals or local column references that are bound to constants during execution.

EXPLAIN output for applicable queries will change from full table or index scan to range scan. Changes are also visible by checking the values of the Handler_read_first, Handler_read_key, and Handler_read_next status variables.