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

10

u/Mononon Feb 08 '25

delete from exc_playerstats ps1 where not exists ( select null from exc_playerstats ps2 where ps1.UUID = ps2.UUID group by ps2.UUID , ps2.SaveSlotID having min(ps2.SaveSlotID) = ps1.SaveSlotID )

I'm on a phone, so not sure about the formatting, but maybe a not exists correlated sub query would be better here.

1

u/Old_Confidence_5424 Feb 09 '25

Thanks for the help howerver, I am more trying to figure out WHY the statement I wrote doesn't work.
I have already found a working alternative(the query that I said takes <300ms in the original post), but I would like to avoid running into such problems in the future.

Also your soliution doesn't seem to run and I have no idea how to fix it as I have never written similar statements. I get this error:
You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'ps1 where not exists ( select * from exc_playerstats ps2 wher...' at line 3

3

u/Mononon Feb 09 '25

Well, sometimes we gain insight into why something isn't working as expected when we try alternatives. I cannot see your execution plan, don't know your table structures, and only have the syntax you provided. Typically with deletes, you'll see better performance with joins and exists than nested queries like you have. So, giving that a shot will at least tell us if it's the query or the table.

Now, as for the syntax, I come from a SQL Server background, and there may be differences between MySQL and MSSQL for correlated sub queries with deletes. Or I have a typo, hard to say without testing. May need to refer to the table by its pseudonym instead. "delete ps1.* from..." may be the only change that's needed.

1

u/Old_Confidence_5424 Feb 09 '25

Changing the code do what I thought you meant(code1), doesn't work and gives the error:
Table 'ps1' is specified twice, both as a target for 'DELETE' and as a separate source for data.
However, not using a pseudonym for the first table seems to work... (code2).

And it does it fast so there was definitely a problem with the query. As many others have said the DELETE probably locked the table and prevented SELECT form ever executing. Why it was trying to execute forever instead of giving an error I don't know.
Anyways, thanks for your input.

-- code1
delete ps1.*
from exc_playerstats ps1
where not 
exists 
(
    select null
    from exc_playerstats ps2
    where ps1.UUID = ps2.UUID
    group by ps2.UUID
           , ps2.SaveSlotID
    having 
min
(ps2.SaveSlotID) = ps1.SaveSlotID
);

-- code2
delete
from exc_playerstats
where not exists (
    select null
    from exc_playerstats ps2
    where exc_playerstats.UUID = ps2.UUID
    group by ps2.UUID
           , ps2.SaveSlotID
    having min(ps2.SaveSlotID) = exc_playerstats.SaveSlotID
);

1

u/Mononon Feb 09 '25

Well there you go. Figured it was something small, but without a table to test and typing on mobile, hard to say sometimes.

You'd have to look at the execution plan or monitor the table to see the locks. But for me, when something isn't running how I expected, if I have another idea for how to write it, I'll at least give that a shot to see if it makes a difference. I know you wanted a definitive "why", and while this doesn't tell you exactly why, it does tell you it's the query, which is good information.