I am doing a delete query which should run pretty much instantly
DELETE FROM TableA WHERE TableA.SomeID IN (SELECT DISTINCT TableB.SomeID FROM TableB WHERE TableB.Something = Blah)
If I execute the query inside the IN statement on its own then it runs instantly. If I then comma separate the results of that and paste them into the outer query then that query runs instantly. However, when I run them together it takes 24 seconds. It's like the inner query is getting executed once per row. I also tried modifying it to use an EXISTS statement but that was also slow. Do I need to resort to a temp table for this?
The following recommendations will help you in your SQL tuning process.
You'll find 3 sections below:
ALTER TABLE `TableA` ADD INDEX `tablea_idx_someid` (`SomeID`); ALTER TABLE `TableB` ADD INDEX `tableb_idx_something_blah` (`Something`,`Blah`);
SELECT 1 FROM TableA WHERE TableA.SomeID IN ( SELECT DISTINCT TableB.SomeID FROM TableB WHERE TableB.Something = TableB.Blah )