Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
5.5, 10.0, 10.1
Description
A query of the following form uses an index properly:
select * from t where (id1, id2) IN ((1, 1));
However, when you add a second (or more) tuple to the IN() clause, like the below, then the query never uses an index:
explain select * from t where (id1, id2) IN ((1, 1),(2,2));
EXPLAIN Outputs:
mysql> explain select * from t where (id1, id2) IN ((1, 1));
|
+------+-------------+-------+-------+---------------+---------+---------+-------------+------+-------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+-------+-------+---------------+---------+---------+-------------+------+-------------+
|
| 1 | SIMPLE | t | const | PRIMARY | PRIMARY | 8 | const,const | 1 | Using index |
|
+------+-------------+-------+-------+---------------+---------+---------+-------------+------+-------------+
|
1 row in set (0.04 sec)
|
 |
mysql> explain select * from t where (id1, id2) IN ((1, 1),(2,2));
|
+------+-------------+-------+-------+---------------+---------+---------+------+------+--------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+-------+-------+---------------+---------+---------+------+------+--------------------------+
|
| 1 | SIMPLE | t | index | NULL | PRIMARY | 8 | NULL | 6 | Using where; Using index |
|
+------+-------------+-------+-------+---------------+---------+---------+------+------+--------------------------+
|
1 row in set (0.03 sec)
|
Test Case:
create table t (id1 int, id2 int, primary key (id1, id2)) engine=innodb;
|
insert into t values (1,1),(2,2),(3,3),(1,2),(1,3),(2,3);
|
explain select * from t where (id1, id2) IN ((1, 1));
|
explain select * from t where (id1, id2) IN ((1, 1),(2,2));
|
The first EXPLAIN plan uses an index. The second EXPLAIN plan does not.