title: Why MySQL DELETE is unable to use indexes on sub-queries?
body: <p>As established in some <a href="https://stackoverflow.com/questions/4471277/mysql-delete-from-with-subquery-as-condition">other</a> <a href="https://stackoverflow.com/questions/7361174/very-slow-delete-on-mysql-base-with-subquery">questions</a> here, using "subquery" in MySQL delete causes it to be slower, while identical "select" query performs fast:</p>
  
  <pre><code>MariaDB [as_01_import]&gt; 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]&gt; 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)
  </code></pre>
  
  <p>Knowing that JOIN can use indexes, I would like to ask experts:</p>
  
  <p>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?</p>