MySQL Bugs: #91723: Bad select+order by+limit performance in 5.7

Bug #91723	Bad select+order by+limit performance in 5.7
Submitted:	20 Jul 2018 8:42	Modified:	10 Aug 2018 12:46
Reporter:	Nikolai Ikhalainen	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S3 (Non-critical)
Version:	5.7.22	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
First query (without force index) scans too many records for 5.7 and query execution time is 17 seconds:
| Handler_read_first    | 0       |
| Handler_read_key      | 1       |
| Handler_read_last     | 0       |
| Handler_read_next     | 0       |
| Handler_read_prev     | 2259839 |
| Handler_read_rnd      | 0       |
| Handler_read_rnd_next | 0       |

For 8.0.15 both queries executed instantly with just Handler_read_key = 1.

How to repeat:
drop table if exists t;
create table t(id int not null primary key auto_increment, c1 int not null, c2 int, key idx(c1));
insert t (c1, c2) values (1,1),(1,1),(1,1),(1,1);
insert t (c1, c2) select 1, 1 from t t1, t t2, t t3, t t4, t t5, t t6, t t7, t t8, t t9, t t10;
insert t (c1, c2) select 4, 1 from t;
insert t (c1, c2) select 4, 1 from t;
flush status;select * from t  where c1 = 4 and id <= 2000000 order by c1 desc, id desc limit 1;show status like 'Handler_read%';
flush status;select * from t force key(idx) where c1 = 4 and id <= 2000000 order by c1 desc, id desc limit 1;show status like 'Handler_read%';

Suggested fix:
Make Handler_read_key = 1 possible on 5.7 without FORCE hint

Hello Nikolai,

Thank you for the report!

Thanks,
Umesh

Posted by developer:
 
This is not a bug.  The bad query plan is a result of that statistics have not yet been updated after the preceeding bulk insert.  As can be seen below, if ANALYZE TABLE is first run, an efficient query plan is selected.

mysql> select version();
+-----------+
| version() |
+-----------+
| 5.7.22    |
+-----------+
1 row in set (0,00 sec)

mysql> create table t(id int not null primary key auto_increment, c1 int not null,
    -> c2 int, key idx(c1));
Query OK, 0 rows affected (0,01 sec)

mysql> insert t (c1, c2) values (1,1),(1,1),(1,1),(1,1);
Query OK, 4 rows affected (0,00 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> insert t (c1, c2) select 1, 1 from t t1, t t2, t t3, t t4, t t5, t t6, t t7,
    -> t t8, t t9, t t10;
Query OK, 1048576 rows affected (15,05 sec)
Records: 1048576  Duplicates: 0  Warnings: 0

mysql> insert t (c1, c2) select 4, 1 from t;
Query OK, 1048580 rows affected (16,04 sec)
Records: 1048580  Duplicates: 0  Warnings: 0

mysql> insert t (c1, c2) select 4, 1 from t;
Query OK, 2097160 rows affected (32,86 sec)
Records: 2097160  Duplicates: 0  Warnings: 0

mysql> analyze table t;
+------------+---------+----------+----------+
| Table      | Op      | Msg_type | Msg_text |
+------------+---------+----------+----------+
| dbt3_sf1.t | analyze | status   | OK       |
+------------+---------+----------+----------+
1 row in set (0,11 sec)

mysql> flush status;select * from t  where c1 = 4 and id <= 2000000 order by c1
Query OK, 0 rows affected (0,00 sec)

    -> desc, id desc limit 1;show status like 'Handler_read%';
+---------+----+------+
| id      | c1 | c2   |
+---------+----+------+
| 2000000 |  4 |    1 |
+---------+----+------+
1 row in set (0,01 sec)

+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| Handler_read_first    | 0     |
| Handler_read_key      | 1     |
| Handler_read_last     | 0     |
| Handler_read_next     | 0     |
| Handler_read_prev     | 0     |
| Handler_read_rnd      | 0     |
| Handler_read_rnd_next | 0     |
+-----------------------+-------+
7 rows in set (0,00 sec)