Hi, Rhys.Campbell! On Jul 25, Rhys.Campbell@swisscom.com wrote:
Hello All,
I'm having a bit of an issue with the RETURNING feature of DELETE<https://mariadb.com/kb/en/mariadb/delete/>. This was introduced in MariaDB 10.0.5. Specifically there seems to be an issue when using aliases with the RETURNING keyword.
It involves a query like this. A user came to me with queries of this form that were running slowly. Here is the explain plan...
EXPLAIN DELETE from t1 where id in (select id from t1 where acc_id = 9999) \G ... You can see it's being forced to run a table scan and run the subquery for each row. This was fixed by properly aliasing the query...
EXPLAIN EXTENDED DELETE uc from t1 AS uc where uc. id in (select p. id from t2 p where p.acc_id = 9999) \G ...
The first query is, internally "single-table DELETE", the second is "multi-table DELETE". I've reported different plans as a bug https://jira.mariadb.org/browse/MDEV-10447 DELETE ... RETURNING does not support multi-table delete, it's documented here: https://mariadb.com/kb/en/mariadb/delete/ I've created a task to fix it. https://jira.mariadb.org/browse/MDEV-10448 Regards, Sergei Chief Architect MariaDB and security@mariadb.org