Sub-optimal query plan is picked from range vs another range vs ref

Description

1 2 3 4 5 6 CREATE TABLE tbl ( ... PRIMARY KEY (pk), KEY key1 (key1,pk) ) ENGINE=InnoDB;

And two query plans:

1 2 3 4 5 6 7 # The bad one: mysql> explain SELECT * FROM tbl1 WHERE key1 = const1 AND pk > const2A limit 1,1; +----+-------------+-------+------+---------------+------+---------+-------+-------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+-------+-------+-------------+ | 1 | SIMPLE | tbl1 | ref | PRIMARY,key1 | key1 | 9 | const | 24398 | Using where | +----+-------------+-------+------+---------------+------+---------+-------+-------+-------------+
1 2 3 4 5 6 7 # The good one one: mysql> explain SELECT * FROM tbl1 WHERE key1 = const1 AND pk > const2B limit 1,1; +----+-------------+-------+-------+----------------+------+---------+------+---------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------+----------------+------+---------+------+---------+-------------+ | 1 | SIMPLE | tbl1 | range | PRIMARY,key1 | key1 | 17 | NULL | 1052604 | Using where | +----+-------------+-------+-------+-------------- -+------+---------+------+---------+-------------+

optimizer's choice between Good and Bad query plans depends on the values of const2A and const2B.

Note that both query plans use the same index, but the Good plan uses more key
parts. The point is that Good plan should always be used, because it scans a
proper subset of rows that the Bad plan is scanning.

Environment

None

Status

Assignee

Sergei Petrunia

Reporter

Sergei Petrunia

Labels

None

External issue ID

None

External issue ID

None

Components

Fix versions

Affects versions

10.0
10.1
5.5

Priority

Major