Reputation: 111
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?
Upvotes: 2
Views: 527
Reputation: 1269633
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).
Upvotes: 2