[MDEV-10454] Using second tuple nullifies use of an index when using 2-part PK in IN() Created: 2016-07-27  Updated: 2017-04-24  Resolved: 2017-04-04

Status: Closed
Project: MariaDB Server
Component/s: Optimizer
Affects Version/s: 5.5, 10.0, 10.1
Fix Version/s: 10.2.5

Type: Bug Priority: Major
Reporter: Chris Calender (Inactive) Assignee: Igor Babaev
Resolution: Fixed Votes: 2
Labels: upstream-fixed


 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.

 Comments   
Comment by Chris Calender (Inactive) [ 2016-07-27 ]

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)

Comment by Chris Calender (Inactive) [ 2016-07-27 ]

Or is it somehow using an index? But "possible_keys" reports NULL.

Comment by Elena Stepanova [ 2016-07-29 ]

Reproducible on 5.5 - 10.2 and on MySQL 5.6. Not reproducible on MySQL 5.7.

Comment by Sergei Petrunia [ 2016-07-30 ]

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"

Comment by Oleksandr Byelkin [ 2017-04-03 ]

see e-mail from 15.03.2017 with the review suggestion.

Comment by Igor Babaev [ 2017-04-04 ]

A patch to solve this problem was pushed into the 10.2 tree.

Generated at Thu Feb 08 07:42:20 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.