Bug #92850 Bad select+order by+limit performance in 5.7
Submitted: 19 Oct 2018 0:46 Modified: 22 Oct 2018 5:29
Reporter: Sveta Smirnova (OCA) Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S3 (Non-critical)
Version:5.7.23 OS:Any
Assigned to: CPU Architecture:Any

[19 Oct 2018 0:46] Sveta Smirnova
Description:
Similar to bug #91723 but repeatable after ANALYZE TABLE run.

How to repeat:
create table test(
seq int not null primary key auto_increment,
grp int not null,
txt varchar(64),
key ix_test(grp),
key ix_test2(grp,seq)
) engine=innodb;
insert into test (grp, txt) select 1, md5(rand()) from dual;
insert into test (grp, txt) select 1, md5(rand()) from test;
insert into test (grp, txt) select 1, md5(rand()) from test;
<repeat few times to insert more data>

mysql> analyze table test;
+-----------+---------+----------+----------+
| Table     | Op      | Msg_type | Msg_text |
+-----------+---------+----------+----------+
| test.test | analyze | status   | OK       |
+-----------+---------+----------+----------+
1 row in set (0.13 sec)

mysql> select * from test  where grp = 4 and seq <= 1851933 order by grp desc, seq desc limit 1;
+---------+-----+----------------------------------+
| seq     | grp | txt                              |
+---------+-----+----------------------------------+
| 1851933 |   4 | 64cbf702af49857b19c8d5890eb476a2 |
+---------+-----+----------------------------------+
1 row in set (17.82 sec)

mysql> select * from test use index(ix_test) where grp = 4 and seq <= 1851933 order by grp desc, seq desc limit 1;
+---------+-----+----------------------------------+
| seq     | grp | txt                              |
+---------+-----+----------------------------------+
| 1851933 |   4 | 64cbf702af49857b19c8d5890eb476a2 |
+---------+-----+----------------------------------+
1 row in set (17.37 sec)

mysql> select * from test force index(ix_test) where grp = 4 and seq <= 1851933 order by grp desc, seq desc limit 1;
+---------+-----+----------------------------------+
| seq     | grp | txt                              |
+---------+-----+----------------------------------+
| 1851933 |   4 | 64cbf702af49857b19c8d5890eb476a2 |
+---------+-----+----------------------------------+
1 row in set (0.01 sec)

Notice query execution time difference.

EXPLAIN is also different:

mysql> explain select * from test  where grp = 4 and seq <= 1851933 order by grp desc, seq desc limit 1;
+----+-------------+-------+------------+------+--------------------------+---------+---------+-------+---------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys            | key     | key_len | ref   | rows    | filtered | Extra       |
+----+-------------+-------+------------+------+--------------------------+---------+---------+-------+---------+----------+-------------+
|  1 | SIMPLE      | test  | NULL       | ref  | PRIMARY,ix_test,ix_test2 | ix_test | 4       | const | 1044500 |    50.00 | Using where |
+----+-------------+-------+------------+------+--------------------------+---------+---------+-------+---------+----------+-------------+
1 row in set, 1 warning (0.05 sec)

mysql> explain select * from test use index(ix_test) where grp = 4 and seq <= 1851933 
+----+-------------+-------+------------+------+---------------+---------+---------+-------+---------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key     | key_len | ref   | rows    | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+---------+---------+-------+---------+----------+-------------+
|  1 | SIMPLE      | test  | NULL       | ref  | ix_test       | ix_test | 4       | const | 1044500 |    33.33 | Using where |
+----+-------------+-------+------------+------+---------------+---------+---------+-------+---------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql> explain select * from test force index(ix_test) where grp = 4 and seq <= 1851933 
+----+-------------+-------+------------+-------+---------------+---------+---------+------+---------+----------+-----------------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref  | rows    | filtered | Extra                 |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+---------+----------+-----------------------+
|  1 | SIMPLE      | test  | NULL       | range | ix_test       | ix_test | 8       | NULL | 1044500 |   100.00 | Using index condition |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+---------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

Handler statistics:

mysql> flush status;                                                               Query OK, 0 rows affected (0.00 sec)

mysql> select * from test use index(ix_test) where grp = 4 and seq <= 1851933 order by grp desc, seq desc limit 1;
+---------+-----+----------------------------------+
| seq     | grp | txt                              |
+---------+-----+----------------------------------+
| 1851933 |   4 | 64cbf702af49857b19c8d5890eb476a2 |
+---------+-----+----------------------------------+
1 row in set (17.71 sec)

mysql> show status like 'Handler_read%';                                           +-----------------------+--------+
| Variable_name         | Value  |
+-----------------------+--------+
| Handler_read_first    | 0      |
| Handler_read_key      | 1      |
| Handler_read_last     | 0      |
| Handler_read_next     | 0      |
| Handler_read_prev     | 572863 |
| Handler_read_rnd      | 0      |
| Handler_read_rnd_next | 0      |
+-----------------------+--------+
7 rows in set (0.00 sec)

mysql> flush status;                                                               Query OK, 0 rows affected (0.00 sec)

mysql> select * from test force index(ix_test) where grp = 4 and seq <= 1851933 order by grp desc, seq desc limit 1;
+---------+-----+----------------------------------+
| seq     | grp | txt                              |
+---------+-----+----------------------------------+
| 1851933 |   4 | 64cbf702af49857b19c8d5890eb476a2 |
+---------+-----+----------------------------------+
1 row in set (0.01 sec)

mysql> show status like 'Handler_read%';                                           +-----------------------+-------+
| 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)
[19 Oct 2018 0:47] Sveta Smirnova
test case for MTR

Attachment: bug91723.test (application/octet-stream, text), 2.63 KiB.

[19 Oct 2018 1:57] Dongchan Sung
Hi,

It seems to be a problem that the explain in the "use index query" shows "key_len" refers to 4 bytes only. The force index refers to all the columns of the index as 8.

Best Regard,
Chan.
[22 Oct 2018 5:29] Umesh Shastry
Hello Sveta,

Thank you for the report and test case.
Observed this with 5.7.23 build.

regards,
Umesh