Description:
When WHERE clause is specified with (col1,col2) IN ((x,y)) rather than col1=x AND col2=y, indexes are not used for the query.
How to repeat:
Table has 200k rows, a=1..200000, (b,c)=(1...200,1..1000):
mysql> explain select a from inno where (a,b) in ((1,1));
+----+-------------+-------+-------+---------------+------+---------+------+--------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+------+---------+------+--------+--------------------------+
| 1 | SIMPLE | inno | index | NULL | b | 5 | NULL | 232813 | Using where; Using index |
+----+-------------+-------+-------+---------------+------+---------+------+--------+--------------------------+
1 row in set (0.00 sec)
mysql> explain select a from inno where (b,c) in ((1,1));
+----+-------------+-------+-------+---------------+------+---------+------+--------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+------+---------+------+--------+--------------------------+
| 1 | SIMPLE | inno | index | NULL | b_2 | 10 | NULL | 232813 | Using where; Using index |
+----+-------------+-------+-------+---------------+------+---------+------+--------+--------------------------+
1 row in set (0.01 sec)
mysql> explain select a from inno where a=1 and b=1;
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+
| 1 | SIMPLE | inno | const | PRIMARY,b,b_2 | PRIMARY | 4 | const | 1 | |
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+
1 row in set (0.00 sec)
mysql> explain select a from inno where b=1 and c=1;
+----+-------------+-------+------+---------------+------+---------+-------------+------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+-------------+------+--------------------------+
| 1 | SIMPLE | inno | ref | b,b_2 | b_2 | 10 | const,const | 1 | Using where; Using index |
+----+-------------+-------+------+---------------+------+---------+-------------+------+--------------------------+
1 row in set (0.09 sec)
mysql> show create table inno \G
*************************** 1. row ***************************
Table: inno
Create Table: CREATE TABLE `inno` (
`a` int(11) NOT NULL,
`b` int(11) default NULL,
`c` int(11) default NULL,
PRIMARY KEY (`a`),
KEY `b` (`b`),
KEY `b_2` (`b`,`c`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
Suggested fix:
use indexes for IN()
Description: When WHERE clause is specified with (col1,col2) IN ((x,y)) rather than col1=x AND col2=y, indexes are not used for the query. How to repeat: Table has 200k rows, a=1..200000, (b,c)=(1...200,1..1000): mysql> explain select a from inno where (a,b) in ((1,1)); +----+-------------+-------+-------+---------------+------+---------+------+--------+--------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------+---------------+------+---------+------+--------+--------------------------+ | 1 | SIMPLE | inno | index | NULL | b | 5 | NULL | 232813 | Using where; Using index | +----+-------------+-------+-------+---------------+------+---------+------+--------+--------------------------+ 1 row in set (0.00 sec) mysql> explain select a from inno where (b,c) in ((1,1)); +----+-------------+-------+-------+---------------+------+---------+------+--------+--------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------+---------------+------+---------+------+--------+--------------------------+ | 1 | SIMPLE | inno | index | NULL | b_2 | 10 | NULL | 232813 | Using where; Using index | +----+-------------+-------+-------+---------------+------+---------+------+--------+--------------------------+ 1 row in set (0.01 sec) mysql> explain select a from inno where a=1 and b=1; +----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+ | 1 | SIMPLE | inno | const | PRIMARY,b,b_2 | PRIMARY | 4 | const | 1 | | +----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+ 1 row in set (0.00 sec) mysql> explain select a from inno where b=1 and c=1; +----+-------------+-------+------+---------------+------+---------+-------------+------+--------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+-------------+------+--------------------------+ | 1 | SIMPLE | inno | ref | b,b_2 | b_2 | 10 | const,const | 1 | Using where; Using index | +----+-------------+-------+------+---------------+------+---------+-------------+------+--------------------------+ 1 row in set (0.09 sec) mysql> show create table inno \G *************************** 1. row *************************** Table: inno Create Table: CREATE TABLE `inno` ( `a` int(11) NOT NULL, `b` int(11) default NULL, `c` int(11) default NULL, PRIMARY KEY (`a`), KEY `b` (`b`), KEY `b_2` (`b`,`c`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 1 row in set (0.00 sec) Suggested fix: use indexes for IN()