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]
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.
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.