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
• Status: Verified
• Category: MySQL Server: Optimizer
• Severity: S5 (Performance)
• Version: 8.0
• OS: Any
• 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.