| 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: | |
| Category: | MySQL Server: Optimizer | Severity: | S2 (Serious) |
| Version: | 5.7.26 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[29 May 2019 12:34]
MySQL Verification Team
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)
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.