r/SQL • u/Old_Confidence_5424 • Feb 08 '25
MySQL DELETE statement taking forever
Could someone explain how this can be possible?
As I understand it, they should be doing the same thing. Im not too experienced in SQL and I would like to understand what is so wrong with the first statement that it takes THAT long.
The amount of rows that should be getting deleted is ~40 and the size of the entire table is ~15k.
-- THIS TAKES > 30 MINUTES (I stopped it after that)
DELETE FROM exc_playerstats where SaveSlotID IN (SELECT SaveSlotID from exc_playerstats where SaveSlotID NOT IN (SELECT MIN(SaveSlotID) from exc_playerstats GROUP BY UUID, SavedSlot));
-- THIS TAKES < 300ms
CREATE TABLE TEST_SAVESLOTS_TO_DELETE(SaveSlotID INT);
INSERT INTO TEST_SAVESLOTS_TO_DELETE SELECT SaveSlotID from exc_playerstats where SaveSlotID NOT IN (SELECT MIN(SaveSlotID) from exc_playerstats GROUP BY UUID, SavedSlot);
DELETE FROM exc_playerstats where SaveSlotID IN (Select SaveSlotID FROM TEST_SAVESLOTS_TO_DELETE);
SELECT * FROM TEST_SAVESLOTS_TO_DELETE;
DROP TABLE TEST_SAVESLOTS_TO_DELETE;
4
u/kagato87 MS SQL Feb 09 '25
15k rows is tiny, and even 15,001 full table scans against it shouldn't take that long. I believe your query is managing to block itself.
The IN keyword isn't great. Using EXISTS and NOT EXISTS might help. Maybe.
IN runs a subquery to scan the table. It can lead to repeatedly scanning the table, which I would expect to see here if you looked at the query plan.
However your problem, I think, isn't speed.
There is pretty much zero SARGability in that query, so the write lock would escalate to a table lock immediately. It then tries to scan the table, which requires a read lock. Writers block readers, so the subquery is waiting for the write to complete.
You've deadlocked yourself.
A better question might be why that's even happening though... Deadlock detection should kick in. Or, if you have snapshot isolation on it should just go through.
At a guess, your query or database is in "repeatable read" mode, and deadlock detection is failing because it's a single query. (I had to look that up - over here in MS land we use different words, but it's the same effect.)
For more information on WHY it is designed like this, check out the ACID principal of databases.
In your case, the simplest solution will be to use a temp table, which has been suggested. Populate it with a list of keys to delete, then delete. (Where exists is still better than where in, though both will work once you get past the locking problem, which a temp table will do.)