Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
5.5(EOL), 10.0(EOL), 10.1(EOL)
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.
Attachments
Activity
Or is it somehow using an index? But "possible_keys" reports NULL.
Reproducible on 5.5 - 10.2 and on MySQL 5.6. Not reproducible on MySQL 5.7.
Making range optimizer to handle this is a feature in MySQL 5.7.
Originally requested by Domas@FB:
http://bugs.mysql.com/bug.php?id=31188
http://bugs.mysql.com/bug.php?id=16247
http://dev.mysql.com/worklog/task/?id=7019
http://dev.mysql.com/doc/refman/5.7/en/range-optimization.html , search for "range optimization of row constructor expressions"
I thought MRR might help, if enabled, but it does not seem to make a difference:
mysql> set optimizer_switch='mrr=on,mrr_sort_keys=on';Query OK, 0 rows affected (0.00 sec)mysql> set join_cache_level=6;Query OK, 0 rows affected (0.01 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.00 sec)