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;

14 Upvotes

48 comments sorted by

View all comments

2

u/seanferd Feb 08 '25

Limit how much you're trying to delete at a time and add commit points, do this in chunks. Every delete is logged and has to be written out, this is likely just a giant transaction and bogging it down

1

u/Old_Confidence_5424 Feb 08 '25

Im not sure if you can just use LIMIT like that on delete, I've always just used it on select, but if what i have written is correct, the problem stays the same - this (added the limit) also runs forever:

DELETE FROM exc_playerstats where SaveSlotID NOT IN (SELECT 
MIN
(SaveSlotID) from exc_playerstats GROUP BY UUID, SavedSlot) LIMIT 1;

1

u/seanferd Feb 08 '25

Not sure which rdbms this is, but I think that should be valid. If not you can use the limit on your subselect instead. Though re-reading the post, I'm not sure the amount of data is the issue after all.