Bug #109107 Non optimal query plan chosen for DELETE
Description:
For a range DELETE query, I am getting a bad execution plan, while a select with the same where clause uses an index as expected.
How to repeat:
CREATE TABLE `test1` (
`id` int NOT NULL AUTO_INCREMENT,
`created_at` datetime DEFAULT NULL,
`updated_at` datetime DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `i1` (`created_at`,`updated_at`),
KEY `i2` (`updated_at`,`created_at`)
) ENGINE=InnoDB AUTO_INCREMENT=10001 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
Above table filled with random data.
mysql > select @@version,@@version_comment;
+-----------+------------------------------+
| @@version | @@version_comment |
+-----------+------------------------------+
| 8.0.31 | MySQL Community Server - GPL |
+-----------+------------------------------+
1 row in set (0.00 sec)
mysql > EXPLAIN select * from test1 WHERE (updated_at IS NULL AND created_at <= '2022-05-04 04:04:54') OR (updated_at IS NOT NULL AND updated_at <= '2022-05-04 04:04:54');
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+--------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+--------------------------+
| 1 | SIMPLE | test1 | NULL | range | i1,i2 | i2 | 12 | NULL | 4777 | 100.00 | Using where; Using index |
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)
mysql > EXPLAIN DELETE FROM test1 WHERE (updated_at IS NULL AND created_at <= '2022-05-04 04:04:54') OR (updated_at IS NOT NULL AND updated_at <= '2022-05-04 04:04:54');
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | DELETE | test1 | NULL | ALL | i2 | NULL | NULL | NULL | 9599 | 100.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.01 sec)
mysql > flush status;
Query OK, 0 rows affected (0.00 sec)
mysql > pager md5sum
PAGER set to 'md5sum'
mysql > select * from test1 WHERE (updated_at IS NULL AND created_at <= '2022-05-04 04:04:54') OR (updated_at IS NOT NULL AND updated_at <= '2022-05-04 04:04:54');
1439713402a702f1dcb9fccf0bb2eecd -
4777 rows in set (0.01 sec)
mysql > nopager
PAGER set to stdout
mysql > show status like 'ha%';
+----------------------------+-------+
| Variable_name | Value |
+----------------------------+-------+
| Handler_commit | 1 |
| Handler_delete | 0 |
| Handler_discover | 0 |
| Handler_external_lock | 2 |
| Handler_mrr_init | 0 |
| Handler_prepare | 0 |
| Handler_read_first | 0 |
| Handler_read_key | 2 |
| Handler_read_last | 0 |
| Handler_read_next | 4777 |
| Handler_read_prev | 0 |
| Handler_read_rnd | 0 |
| Handler_read_rnd_next | 0 |
| Handler_rollback | 0 |
| Handler_savepoint | 0 |
| Handler_savepoint_rollback | 0 |
| Handler_update | 0 |
| Handler_write | 0 |
+----------------------------+-------+
18 rows in set (0.08 sec)
mysql > flush status;
Query OK, 0 rows affected (0.00 sec)
mysql > DELETE FROM test1 WHERE (updated_at IS NULL AND created_at <= '2022-05-04 04:04:54') OR (updated_at IS NOT NULL AND updated_at <= '2022-05-04 04:04:54');
Query OK, 4777 rows affected (0.10 sec)
mysql > show status like 'ha%';
+----------------------------+-------+
| Variable_name | Value |
+----------------------------+-------+
| Handler_commit | 2 |
| Handler_delete | 4777 |
| Handler_discover | 0 |
| Handler_external_lock | 2 |
| Handler_mrr_init | 0 |
| Handler_prepare | 2 |
| Handler_read_first | 1 |
| Handler_read_key | 1 |
| Handler_read_last | 0 |
| Handler_read_next | 0 |
| Handler_read_prev | 0 |
| Handler_read_rnd | 0 |
| Handler_read_rnd_next | 10001 |
| Handler_rollback | 0 |
| Handler_savepoint | 0 |
| Handler_savepoint_rollback | 0 |
| Handler_update | 0 |
| Handler_write | 0 |
+----------------------------+-------+
18 rows in set (0.00 sec)
-- reload table and use index hint instead as a workaround:
mysql > EXPLAIN DELETE test1.* FROM test1 USE KEY(i2) WHERE (updated_at IS NULL AND created_at <= '2022-05-04 04:04:54') OR (updated_at IS NOT NULL AND updated_at <= '2022-05-04 04:04:54');
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-------------+
| 1 | DELETE | test1 | NULL | range | i2 | i2 | 12 | NULL | 4777 | 100.00 | Using where |
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
mysql > flush status;
Query OK, 0 rows affected (0.00 sec)
mysql > DELETE test1.* FROM test1 USE KEY(i2) WHERE (updated_at IS NULL AND created_at <= '2022-05-04 04:04:54') OR (updated_at IS NOT NULL AND updated_at <= '2022-05-04 04:04:54');
Query OK, 4777 rows affected (0.05 sec)
mysql > show status like 'ha%';
+----------------------------+-------+
| Variable_name | Value |
+----------------------------+-------+
| Handler_commit | 2 |
| Handler_delete | 4777 |
| Handler_discover | 0 |
| Handler_external_lock | 2 |
| Handler_mrr_init | 0 |
| Handler_prepare | 2 |
| Handler_read_first | 0 |
| Handler_read_key | 2 |
| Handler_read_last | 0 |
| Handler_read_next | 4777 |
| Handler_read_prev | 0 |
| Handler_read_rnd | 0 |
| Handler_read_rnd_next | 0 |
| Handler_rollback | 0 |
| Handler_savepoint | 0 |
| Handler_savepoint_rollback | 0 |
| Handler_update | 0 |
| Handler_write | 0 |
+----------------------------+-------+
18 rows in set (0.00 sec)
Suggested fix:
A proper plan should be chosen regardless of DELETE or SELECT.
Portions of this website are copyright © 2001, 2002 The PHP Group
Page generated in 0.043 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.