DELETE with SELECT sub-query gets stuck

Question

The following delete statement causing mysql to stuck:

DELETE FROM domains WHERE id IN (SELECT domain_id FROM domains_groups WHERE group_id = 9);

While this statement, which does the same, works:

DELETE FROM domains WHERE id IN (select id from (SELECT domain_id as id FROM domains_groups WHERE group_id = 9) a);

Can anyone explain why?

score 2 · Accepted Answer · answered Dec 17 '14 at 13:34

The reason has to do with the optimization of SQL queries. Your first version is evaluating the subquery for each row -- although this has been fixed in more recent versions of MySQL. The second version is creating a temporary table, so this goes much faster.

I would recommend using exists or join:

DELETE d FROM domains d
    WHERE EXISTS (SELECT 1 FROM domains_groups dg WHERE dg.group_id = 9 AND dg.domain_id  = d.id);

Or:

DELETE d
    FROM domains d JOIN
         domaings_groups dg
         ON dg.group_id = 9 AND dg.domain_id  = d.id;

I would also recommend an index either domain_groups(domain_id, group_id) (for the above two versions) or domain_groups(group_id, domain_id) (for the in version).

DELETE with SELECT sub-query gets stuck

1 Answers1