1

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?

Community
  • 1
  • 1
romaninsh
  • 10,606
  • 4
  • 50
  • 70

2 Answers2

2

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.

Community
  • 1
  • 1
Drew
  • 24,851
  • 10
  • 43
  • 78
2

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.

Rick James
  • 135,179
  • 13
  • 127
  • 222
  • I think Rick was focusing on some other aspect of it, like doing it differently, as opposed to explaining the subquery behavior. That said, far be it from me to not this [Docs Multi-Table Delete](http://stackoverflow.com/documentation/mysql/1487/delete/8768/multi-table-deletes#t=201607292124449831703). – Drew Jul 29 '16 at 21:35