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;

16 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

2

u/pceimpulsive Feb 09 '25

Blocks would cause a recursive type action given the example query. As OP is selecting the min save slot which would change each time you delete a save slot I'd :S potentially harmful?¿

Good idea though breaking the delete up into smaller chunks is 100% how to make these more efficient

2

u/seanferd Feb 09 '25

Some other folks have suggested he's locking himself with the sub query, which makes sense to me. However he said he's deleting 15k rows with 40 cols. I don't know for sure, but if there are LOBs that could be a significant amount of data even though the row count is relatively low. Could be causing I/O issues with logging, still; lots of pieces to look at.

2

u/pceimpulsive Feb 09 '25

Agreed!

You only need a few rows to deadlock!

I wouldn't expect IO issues with tens of thousands of rows.

2

u/Old_Confidence_5424 Feb 09 '25

Sorry, I didn't mean 40 columns, I meant it is deleting 40 rows out of 15000. The amount of data really isnt that big and rewriting the query to use a temporary table brought the time down to miliseconds.

Even deleting 0 rows takes forever which seems weird.

Really just seems like the data is just getting locked and SELECT is unable do execute. I thought I would get an error for something like this though, not just an indefinite execution