Bug #16247 Compound (cola,colb) IN ((x,y)) statements do not use indexes.
Submitted: 6 Jan 2006 9:03 Modified: 4 Sep 2006 12:55
Reporter: Domas Mituzas Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S3 (Non-critical)
Version:5.0.18 OS:Linux (Linux, MacOSX)
Assigned to: Assigned Account CPU Architecture:Any

[6 Jan 2006 9:03] Domas Mituzas
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()
[4 Sep 2006 12:55] Igor Babaev
This is a duplicate of the performance issue #16081.
[7 Jan 2010 21:33] Mark Callaghan
I disagree with Igor. I think http://bugs.mysql.com/bug.php?id=31188 is a duplicate of this.