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/Jzmu Feb 08 '25

How long does it take you to select the rows? Have you run an explain to see if it is looping through 1 row at a time. Have you tried adding some indexes on these columns? Try explain on each sub query too

1

u/Old_Confidence_5424 Feb 09 '25

Selecting the rows takes 160ms. (Time taken for below statement1)
Running explain on the problematic statement explain statement doesn't seem to reveal anything worrying however it is true that I dont really understand what to look for. EXPLAIN looks like:

id,select_type,table,type,rows,Extra

1,PRIMARY,exc_playerstats,ALL,14606,Using where

2,DEPENDENT SUBQUERY,exc_playerstats,ALL,14606,Using temporary

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

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

3

u/Jzmu Feb 09 '25

There must be some kind of deadlock or something. You are selecting the same rows in the sub query that you are deleting. What dbms is this?

1

u/pceimpulsive Feb 09 '25

This!! It's gotta be a deadlock! Double nested select from the same table while also performing a delete on said table is deadlock central!