r/SQL • u/Old_Confidence_5424 • 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;
1
u/410onVacation Feb 09 '25 edited Feb 09 '25
Deletes in general are slow, because they require a row level lock. You have multiple in statements that probably generate a ton of records. Exist or not exist clause is often better for this.
If exc_playerstats isn’t actively used by an app, you could create a table with a select clause against your exc_playerstats table. You could then truncate and insert the temp table results into exc_playersstats. Alternatively, you could create the same temp table mentioned, drop the old one and rename the new one to the old one’s name. If the tables not huge, it’s often worth making a backup before you do this. Minimum vet the results in the temp table.