Bug #95543 optimizer prefers index for order by rather than filtering - (70x slower)

Submitted: 27 May 2019 12:37
Reporter: Shane Bester (Platinum Quality Contributor) Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S2 (Serious)
Version:5.7.26 OS:Any
Assigned to: CPU Architecture:Any

[27 May 2019 12:37] Shane Bester
Description:
Tested to affect 5.7.26 and 8.0.16
Given this table:

create table `t` (
 `a` int,
 `b` int unsigned not null default '0',
 `c` int unsigned not null default '0',
 `d` varchar(100),
 `e` date,
 `f` date,
 `g` tinyint(3) unsigned not null default '0',
 primary key (`b`),
 key `d` (`d`(10),`g`),
 key `c` (`c`,`g`),
 key `a` (`a`,`g`)
) engine=innodb default charset=latin1 row_format=dynamic;

This query:
 mysql> explain select * from t where d not in('ab1','ab3') and a='-10' and e<='2019-04-01' and f>= '2019-06-01' and d='ab2' order by c,d limit 50 offset 1;
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | t     | NULL       | index | d,a           | c    | 5       | NULL | 5083 |     0.00 | Using where |
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

Index "c" is not the best to use.   Either index "a" or "d" is much better,  according to the stats of this:

mysql> select count(*),
    ->  sum(a='-10'),
    ->  sum(a<>'-10'),
    ->  count(distinct a),
    ->  sum(d not in('ab1','ab3')),
    ->  count(distinct d),
    ->  sum(d='ab2'),
    ->  sum(d<>'ab2'),
    ->  sum(e <='2019-04-01'),
    ->  sum(e >'2019-04-01'),
    ->  sum(f>= '2019-06-01'),
    ->  sum(f< '2019-06-01'),
    ->  sum(d not in('ab1','ab3') and a='-10' and e <='2019-04-01' and f>= '2019-06-01' and d='ab2')
    ->  from t\G
*************************** 1. row ***************************
                   count(*): 700001
               sum(a='-10'): 7007    <-----
              sum(a<>'-10'): 692994
          count(distinct a): 100
 sum(d not in('ab1','ab3')): 686200
          count(distinct d): 100
               sum(d='ab2'): 7045    <-----
              sum(d<>'ab2'): 692956
      sum(e <='2019-04-01'): 678968
       sum(e >'2019-04-01'): 21033
      sum(f>= '2019-06-01'): 678375
       sum(f< '2019-06-01'): 21626
sum(d not in('ab1','ab3') and a='-10' and e <='2019-04-01' and f>= '2019-06-01' and d='ab2'): 72
1 row in set (1.15 sec)

How to repeat:
Import the testcase to generate the table and run the queries.
[29 May 2019 12:34] Shane Bester
To make it clear how much badder the wrong index is, here is the Handler counter for each...

Bad index "c" used:
mysql> show status like '%handler%';
+----------------------------+--------+
| Variable_name              | Value  |
+----------------------------+--------+
| Handler_commit             | 1      |
| Handler_delete             | 0      |
| Handler_discover           | 0      |
| Handler_external_lock      | 2      |
| Handler_mrr_init           | 0      |
| Handler_prepare            | 0      |
| Handler_read_first         | 1      |
| Handler_read_key           | 1      |
| Handler_read_last          | 0      |
| Handler_read_next          | 493758 |
| Handler_read_prev          | 0      |
| Handler_read_rnd           | 0      |
| Handler_read_rnd_next      | 0      |
| Handler_rollback           | 0      |
| Handler_savepoint          | 0      |
| Handler_savepoint_rollback | 0      |
| Handler_update             | 0      |
| Handler_write              | 0      |
+----------------------------+--------+
18 rows in set (0.03 sec)

Good index"a" used:
mysql> show status like '%handler%';
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| Handler_commit             | 1     |
| Handler_delete             | 0     |
| Handler_discover           | 0     |
| Handler_external_lock      | 2     |
| Handler_mrr_init           | 0     |
| Handler_prepare            | 0     |
| Handler_read_first         | 0     |
| Handler_read_key           | 1     |
| Handler_read_last          | 0     |
| Handler_read_next          | 7007  |
| Handler_read_prev          | 0     |
| Handler_read_rnd           | 0     |
| Handler_read_rnd_next      | 0     |
| Handler_rollback           | 0     |
| Handler_savepoint          | 0     |
| Handler_savepoint_rollback | 0     |
| Handler_update             | 0     |
| Handler_write              | 0     |
+----------------------------+-------+
18 rows in set (0.00 sec)

Portions of this website are copyright © 2001, 2002 The PHP Group

Page generated in 0.052 sec. using MySQL 8.4.7-u6-cloud

Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.