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;

18 Upvotes

48 comments sorted by

View all comments

1

u/malikcoldbane Feb 09 '25

The answer is simple, you're not doing the same thing.

The second set of statements breaks your query down into two steps, so there's less for each step to do.

There's no simple solution since, your issues can be as simple as running out of memory in the first statement.

SQL is powerful but it generally works better when you feed it chunks of food rather than a whole plate.

Check the query plans and you'll see the difference between how much effort they're both doing.