Bug #109107 Non optimal query plan chosen for DELETE

Submitted: 16 Nov 2022 8:19 Modified: 16 Nov 2022 8:58
Reporter: Przemyslaw Malkowski Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: DML Severity:S5 (Performance)
Version:8.0.31 OS:Any
Assigned to: CPU Architecture:Any
Tags: delete, query plan

[16 Nov 2022 8:19] Przemyslaw Malkowski
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.
[16 Nov 2022 8:20] Przemyslaw Malkowski
Example table dump

Attachment: test1.sql.gz (application/gzip, text), 113.97 KiB.

[16 Nov 2022 8:58] MySQL Verification Team
Hello Przemyslaw,

Thank you for the report. 

Thanks,
Umesh
[22 Nov 2022 14:06] Øystein Grøvlen
This happens because the full optimizer is not run for single-table DELETE/UPDATE queries.  The work-around as shown by your latest example, is to use the multi-table DELETE syntax.  See also https://dev.mysql.com/blog-archive/multi-table-trick/

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.