Bug #102686 The query plan changed cause query too long
Submitted: 22 Feb 12:50 Modified: 24 Feb 14:07
Reporter: shangshang yu Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S5 (Performance)
Version:8.0 OS:Any
Assigned to: CPU Architecture:Any

[22 Feb 12:50] shangshang yu
Description:
The query plan is different between 8.0.18 with 8.0.23.

the query plan in 8.0.22

+----+-------------+--------------------------------------+------------+--------+-----------------------+---------+---------+----------------------------------------------+--------+----------+--------------------------+
| id | select_type | table                                | partitions | type   | possible_keys         | key     | key_len | ref                                          | rows   | filtered | Extra                    |
+----+-------------+--------------------------------------+------------+--------+-----------------------+---------+---------+----------------------------------------------+--------+----------+--------------------------+
|  1 | PRIMARY     | table_DELETE_MultipleTable_Join_1192 | p0,p1      | index  | idx_uni               | idx_n   | 5       | NULL                                         |  59850 |   100.00 | Using index              |
|  1 | PRIMARY     | table_DELETE_MultipleTable_Join_1193 | NULL       | index  | NULL                  | idx_n   | 5       | NULL                                         | 179678 |     0.00 | Using where; Using index |
|  1 | DELETE      | delete_tbtest_no_part                | NULL       | eq_ref | PRIMARY,idx_uni,idx_f | PRIMARY | 4       | test.table_DELETE_MultipleTable_Join_1193.id |      1 |   100.00 | Using where              |
|  1 | PRIMARY     | table_DELETE_MultipleTable_Join_1194 | p0         | ALL    | NULL                  | NULL    | NULL    | NULL                                         |  29925 |   100.00 | Using where              |
|  1 | PRIMARY     | delete_tbtest_no_part_02             | NULL       | index  | NULL                  | idx_n   | 5       | NULL                                         | 179678 |   100.00 | Using where; Using index |
|  3 | SUBQUERY    | detele_tbtest_part_2                 | p0         | const  | PRIMARY,idx_uni       | PRIMARY | 4       | const                                        |      1 |   100.00 | Using index              |
|  2 | SUBQUERY    | detele_tbtest_part_2                 | p0         | const  | PRIMARY,idx_uni       | PRIMARY | 4       | const                                        |      1 |   100.00 | Using index              |
+----+-------------+--------------------------------------+------------+--------+-----------------------+---------+---------+----------------------------------------------+--------+----------+--------------------------+

the query plan in 8.0.18

+----+-------------+--------------------------------------+------------+--------+-----------------------+---------+---------+----------------------------------------------+--------+----------+--------------------------+
| id | select_type | table                                | partitions | type   | possible_keys         | key     | key_len | ref                                          | rows   | filtered | Extra                    |
+----+-------------+--------------------------------------+------------+--------+-----------------------+---------+---------+----------------------------------------------+--------+----------+--------------------------+
|  1 | PRIMARY     | table_DELETE_MultipleTable_Join_1193 | NULL       | index  | NULL                  | idx_n   | 5       | NULL                                         | 179731 |     0.00 | Using where; Using index |
|  1 | DELETE      | delete_tbtest_no_part                | NULL       | eq_ref | PRIMARY,idx_uni,idx_f | PRIMARY | 4       | test.table_DELETE_MultipleTable_Join_1193.id |      1 |   100.00 | Using where              |
|  1 | PRIMARY     | table_DELETE_MultipleTable_Join_1192 | p0,p1      | index  | idx_uni               | idx_n   | 5       | NULL                                         |  59850 |   100.00 | Using where; Using index |
|  1 | PRIMARY     | table_DELETE_MultipleTable_Join_1194 | p0         | ALL    | NULL                  | NULL    | NULL    | NULL                                         |  29925 |   100.00 | Using where              |
|  1 | PRIMARY     | delete_tbtest_no_part_02             | NULL       | index  | NULL                  | idx_n   | 5       | NULL                                         | 179731 |   100.00 | Using where; Using index |
|  3 | SUBQUERY    | detele_tbtest_part_2                 | p0         | const  | PRIMARY,idx_uni       | PRIMARY | 4       | const                                        |      1 |   100.00 | Using index              |
|  2 | SUBQUERY    | detele_tbtest_part_2                 | p0         | const  | PRIMARY,idx_uni       | PRIMARY | 4       | const                                        |      1 |   100.00 | Using index              |
+----+-------------+--------------------------------------+------------+--------+-----------------------+---------+---------+----------------------------------------------+--------+----------+--------------------------+

the query in 8.0.18 is very fast, but it cannot finish in 8.0.22.

How to repeat:
prepare data and execute the query
[22 Feb 12:51] shangshang yu
prepare.sql

Attachment: prepare.sql (application/octet-stream, text), 6.61 KiB.

[22 Feb 12:52] shangshang yu
execute query

Attachment: execute_query.sql (application/octet-stream, text), 2.00 KiB.

[22 Feb 12:54] shangshang yu
The query plan in 8.0.22 is same to 8.0.23
[23 Feb 13:20] MySQL Verification Team
Hi Mr. yu,

Thank you for your bug report.

We do have couple of questions for you.

First of all, did you run that EXPLAIN on the DELETE statement or on the SELECT statement ?????

Second, is there any difference in the speed of SELECT between MySQL 8.0.18 and 8.0.22/23 ???/

Thanks in advance.
[24 Feb 6:19] shangshang yu
Firstly, I did run that EXPLAIN on the DELETE statement.

Secondly, The query finished in seconds on MySQL 8.0.18, but it can not finish on MySQL 8.0.22/23 in several hours.
[24 Feb 14:07] MySQL Verification Team
Hi Mr. yu,

Thank you for your bug report.

We have ran your tests both on 8.0.18 and 8.0.23. While on 8.0.18 it lasts 3 seconds, it took 55 minutes on 8.0.23.

Hence, this is a performance bug.

Verified as a performance bug in 8.0.22 and 8.0.23.