Bug #102686 The query plan changed cause query too long
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
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.