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.
