Bug #102686 The query plan changed cause query too long

Submitted: 22 Feb 2021 12:50 Modified: 24 Feb 2021 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 2021 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 2021 12:51] shangshang yu
prepare.sql

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

[22 Feb 2021 12:52] shangshang yu
execute query

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

[22 Feb 2021 12:54] shangshang yu
The query plan in 8.0.22 is same to 8.0.23
[23 Feb 2021 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 2021 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 2021 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.

Portions of this website are copyright © 2001, 2002 The PHP Group

Page generated in 0.055 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.