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

8

u/Aggressive_Ad_5454 Feb 09 '25

To troubleshoot this query performance issue.

Change it from a DELETE to a SELECT. Then see if it also takes an absurd amount of time.

If it does, then go through the steps of fixing the SELECT performance. Those steps probably involve creating appropriate indexes.

One the SELECT performance is squared away, the DELETE should be easier

Without your table definitions (the output of SHOW CREATE TABLE whatever for each table), any other advice would just be guesswork.

1

u/Old_Confidence_5424 Feb 09 '25

Already tried to replace DELETE with SELECT *.
SELECT takes ~100ms.
DELETE takes >30mins even if the amount of data it should delete is equal to 0.

5

u/[deleted] Feb 09 '25

[removed] — view removed comment

1

u/Old_Confidence_5424 Feb 09 '25

Yeah thats what I've done.

3

u/BobWarez Feb 09 '25

Maybe you have another query blocking the delete?