Hi, Rhys.Campbell! On Jul 25, [email protected] 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 [email protected] _______________________________________________ Mailing list: https://launchpad.net/~maria-discuss Post to : [email protected] Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp
