Description:
When using tuples in an IN clause, MySQL doesn't use an index for select and prefers a table scan for queries where it's obvious to do so.
Rewriting same query to mean the same in different form does use an index.
Provided below is a simple test case reproducing the issue.
(Same problem occurred initially on a large dataset where a full table scan chosen by the optimizer hurt performance)
How to repeat:
mysql> CREATE TABLE t (a INT, b INT, c INT, d INT, KEY IX_A_B (a,b)) ENGINE = INNODB;
Query OK, 0 rows affected (0.01 sec)
mysql>
mysql> INSERT INTO t VALUES (1,10,0,0),(2,10,0,0),(3,10,0,0),(1,20,0,0),(2,20,0,0),(3,20,0,0),
-> (1,30,0,0),(2,30,0,0),(3,30,0,0),(1,40,0,0),(2,40,0,0),(3,40,0,0),
-> (1,50,0,0),(2,50,0,0),(3,50,0,0),(1,60,0,0),(2,60,0,0),(3,60,0,0),
-> (1,70,0,0),(2,70,0,0),(3,70,0,0),(1,80,0,0),(2,80,0,0),(3,80,0,0);
Query OK, 24 rows affected (0.00 sec)
Records: 24 Duplicates: 0 Warnings: 0
mysql> EXPLAIN SELECT * FROM t WHERE (a = 1 AND b = 10) OR (a=2 AND b=10) \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t
type: range
possible_keys: IX_A_B
key: IX_A_B
key_len: 10
ref: NULL
rows: 2
Extra: Using where
1 row in set (0.00 sec)
mysql> EXPLAIN SELECT * FROM t WHERE (a,b) IN ((1,10),(2,10)) \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 24
Extra: Using where
1 row in set (0.00 sec)
Suggested fix:
Have the optimizer treat both queries in the same manner, since they mean the same, and the one using the IN clause is simpler and more succinct.
Description: When using tuples in an IN clause, MySQL doesn't use an index for select and prefers a table scan for queries where it's obvious to do so. Rewriting same query to mean the same in different form does use an index. Provided below is a simple test case reproducing the issue. (Same problem occurred initially on a large dataset where a full table scan chosen by the optimizer hurt performance) How to repeat: mysql> CREATE TABLE t (a INT, b INT, c INT, d INT, KEY IX_A_B (a,b)) ENGINE = INNODB; Query OK, 0 rows affected (0.01 sec) mysql> mysql> INSERT INTO t VALUES (1,10,0,0),(2,10,0,0),(3,10,0,0),(1,20,0,0),(2,20,0,0),(3,20,0,0), -> (1,30,0,0),(2,30,0,0),(3,30,0,0),(1,40,0,0),(2,40,0,0),(3,40,0,0), -> (1,50,0,0),(2,50,0,0),(3,50,0,0),(1,60,0,0),(2,60,0,0),(3,60,0,0), -> (1,70,0,0),(2,70,0,0),(3,70,0,0),(1,80,0,0),(2,80,0,0),(3,80,0,0); Query OK, 24 rows affected (0.00 sec) Records: 24 Duplicates: 0 Warnings: 0 mysql> EXPLAIN SELECT * FROM t WHERE (a = 1 AND b = 10) OR (a=2 AND b=10) \G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: t type: range possible_keys: IX_A_B key: IX_A_B key_len: 10 ref: NULL rows: 2 Extra: Using where 1 row in set (0.00 sec) mysql> EXPLAIN SELECT * FROM t WHERE (a,b) IN ((1,10),(2,10)) \G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: t type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 24 Extra: Using where 1 row in set (0.00 sec) Suggested fix: Have the optimizer treat both queries in the same manner, since they mean the same, and the one using the IN clause is simpler and more succinct.