CODEWITHSUNDEEP
mysqlselectsubquerymariadbsql-delete
romaninsh
romaninsh

Reputation: 10664

Why MySQL DELETE is unable to use indexes on sub-queries?

As established in some other questions here, using "subquery" in MySQL delete causes it to be slower, while identical "select" query performs fast:

MariaDB [as_01_import]> explain select * from invoice_payment where invoice_id in (select id from dochead where system_id = 5786);
+------+-------------+-----------------+------+---------------------------------------+----------------------------+---------+-------------------------+------+-------------+
| id   | select_type | table           | type | possible_keys                         | key                        | key_len | ref                     | rows | Extra       |
+------+-------------+-----------------+------+---------------------------------------+----------------------------+---------+-------------------------+------+-------------+
|    1 | PRIMARY     | dochead         | ref  | PRIMARY,dochead_system_id             | dochead_system_id          | 4       | const                   |  891 | Using index |
|    1 | PRIMARY     | invoice_payment | ref  | invoice_payment_invoice_fk,invoice_id | invoice_payment_invoice_fk | 4       | as_01_import.dochead.id |    1 |             |
+------+-------------+-----------------+------+---------------------------------------+----------------------------+---------+-------------------------+------+-------------+


MariaDB [as_01_import]> explain delete from invoice_payment where invoice_id in (select id from dochead where system_id = 5786);
+------+--------------------+-----------------+-----------------+---------------------------+---------+---------+------+---------+-------------+
| id   | select_type        | table           | type            | possible_keys             | key     | key_len | ref  | rows    | Extra       |
+------+--------------------+-----------------+-----------------+---------------------------+---------+---------+------+---------+-------------+
|    1 | PRIMARY            | invoice_payment | ALL             | NULL                      | NULL    | NULL    | NULL | 1235451 | Using where |
|    2 | DEPENDENT SUBQUERY | dochead         | unique_subquery | PRIMARY,dochead_system_id | PRIMARY | 4       | func |       1 | Using where |
+------+--------------------+-----------------+-----------------+---------------------------+---------+---------+------+---------+-------------+
2 rows in set (0.44 sec)

Knowing that JOIN can use indexes, I would like to ask experts:

What prevents MySQL / MariaDB from using indexes in DELETE with SUBQUERY? Is that an implementation problem or is there a conceptual problem? Are there any plans to solve this? Is the same problem affecting other SQL vendors?

Upvotes: 1

Views: 1691

Answers (2)

Rick James
Rick James

Reputation: 142298

Do not use IN ( SELECT ... ). Instead, use a multi-table DELETE as described here: http://dev.mysql.com/doc/refman/5.5/en/delete.html

Doing so will make use of indexes where applicable.

Upvotes: 2

Drew
Drew

Reputation: 24959

Subqueries are derived tables and are not materialized. They are manifested in temporary tables.

As I wrote in this answer:

The document Derived Tables in MySQL 5.7 describes it well for versions 5.6 and 5.7, where the latter will provide no penalty due to the change in materialized derived table output being incorporated into the outer query. In prior versions, substantial overhead was endured with temporary tables with the derived.

Upvotes: 2

Related Questions