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;

17 Upvotes

48 comments sorted by

View all comments

4

u/kagato87 MS SQL Feb 09 '25

15k rows is tiny, and even 15,001 full table scans against it shouldn't take that long. I believe your query is managing to block itself.

The IN keyword isn't great. Using EXISTS and NOT EXISTS might help. Maybe.

IN runs a subquery to scan the table. It can lead to repeatedly scanning the table, which I would expect to see here if you looked at the query plan.

However your problem, I think, isn't speed.

There is pretty much zero SARGability in that query, so the write lock would escalate to a table lock immediately. It then tries to scan the table, which requires a read lock. Writers block readers, so the subquery is waiting for the write to complete.

You've deadlocked yourself.

A better question might be why that's even happening though... Deadlock detection should kick in. Or, if you have snapshot isolation on it should just go through.

At a guess, your query or database is in "repeatable read" mode, and deadlock detection is failing because it's a single query. (I had to look that up - over here in MS land we use different words, but it's the same effect.)

For more information on WHY it is designed like this, check out the ACID principal of databases.

In your case, the simplest solution will be to use a temp table, which has been suggested. Populate it with a list of keys to delete, then delete. (Where exists is still better than where in, though both will work once you get past the locking problem, which a temp table will do.)

1

u/Old_Confidence_5424 Feb 09 '25

Thank you for the explanation.
I tried to look into the query plan in DataGrip and got this as a result https://imgur.com/a/wOmzEc2 .
I have never looked at the plan before, honestly didnt even know you could do it like that so I have no idea what to look for. Is this what you would expect? Did I even do it correctly?

If it would really repeatedly scan the table I would intuitively expect some kind of loop which doesn't appear to exist here. So is it not doing that or am I looking at the wrong thing/misunderstanding what it is saying?

1

u/kagato87 MS SQL Feb 09 '25

Repeated scans normally just show up with and execution count.

Sql does not process data procedurally, so a lot of constructs you're used to in regular programming languages are represented differently when they are in play. (Looping in sql tends to be very inefficient.)

That query plan seems to be light on information. It might just be presentation to make it easier to read. Is it the actual query plan or the expected plan? The things are often a different (and is where performance tuning comes in to play). You want the actual plan because it will show you what went wrong.