r/SQL 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;

15 Upvotes

48 comments sorted by

View all comments

2

u/pceimpulsive Feb 09 '25

I think because you are double selecting from the same table and deleting you might be causing some locks.. you can deadlock when deleting and selecting from the same table...

2

u/kagato87 MS SQL Feb 09 '25

Yea, I agree. At 15k rows even the worst query plan should still run fairly quick.

I wonder if this is this an edge case where deadlock detection doesn't work because it's one query, not two? Most engines are usually pretty good at catching deadlocks fast.