Which DELETE query is efficient by Time CPU Memory

Question

(I found the same question exists, but was not happy with the detailed specification, so came here for help, forgive me for my ignorance)

DELETE FROM supportrequestresponse # ~3 million records
WHERE SupportRequestID NOT IN (
  SELECT SR.SupportRequestID
  FROM supportrequest AS SR # ~1 million records
)

Or

DELETE SRR
FROM supportrequestresponse AS SRR # ~3 million records
LEFT JOIN supportrequest AS SR
  ON SR.SupportRequestID = SRR.SupportRequestID # ~1 million records
WHERE SR.SupportRequestID IS NULL

Specifics

Database: MySQL
SR.SupportRequestID is INTEGER PRIMARY KEY
SRR.SupportRequestID is INTEGER INDEX
SR.SupportRequestID & SRR.SupportRequestID are not in FOREIGN KEY relation
Both tables contain TEXT columns for subject and message
Both tables are InnoDB

Motive: I am planning to use this with a periodic clean up job, likely to be once an hour or every two hours. It is very important to avoid lengthy operation in order to avoid table locks as this is a very busy database and am already over quota with deadlocks!

EXPLAIN query 1

1   PRIMARY supportrequestresponse  ALL                 410 Using where
2   DEPENDENT SUBQUERY  SR  unique_subquery PRIMARY PRIMARY 4   func    1   Using index

EXPLAIN query 2

1   SIMPLE  SRR ALL                 410 
1   SIMPLE  SR  eq_ref  PRIMARY PRIMARY 4   SRR.SupportRequestID    1   Using where; Using index; Not exists

RUN #2

EXPLAIN query 1

1   PRIMARY supportrequestresponse  ALL                 157209473   Using where
2   DEPENDENT SUBQUERY  SR  unique_subquery PRIMARY PRIMARY 4   func    1   Using index; Using where; Full scan on NULL key

EXPLAIN query 2

1   SIMPLE  SRR ALL                 157209476   
1   SIMPLE  SR  eq_ref  PRIMARY PRIMARY 4   SRR.SupportRequestID    1   Using where; Using index; Not exists

Admin · Answer

I suspect it would be quicker to create a new table, retaining just the rows you wish to keep. Then drop the old table. Then rename the new table.

user_1169062 · Answer

I don't know how to describe this, but this worked as an answer to my case; an unbelievable one!

DELETE SRR
FROM supportrequestresponse AS SRR

LEFT JOIN (
    SELECT SRR3.SupportRequestResponseID
    FROM supportrequestresponse AS SRR3
    LEFT JOIN supportrequest AS SR ON SR.SupportRequestID = SRR3.SupportRequestID
    WHERE SR.SupportRequestID IS NULL
    LIMIT 999
) AS SRR2 ON SRR2.SupportRequestResponseID = SRR.SupportRequestResponseID

WHERE SRR2.SupportRequestResponseID IS NOT NULL;

... # Same piece of SQL
... # Same piece of SQL
... #999 Same piece of SQL

A fork of the second pattern looks/feels appropriate than having to let MySQL match each row against a dynamic list, but this is the minor fact. I just limited the row selection to 999 rows at once only, that lets the DELETE operation finish in a blink of eye, but most importantly, I repeated the same piece of DELETE SQL 99 times one after another!

This basically made it super comfortable for a Cron job. The x99 statements let the database engine keep the tables NOT LOCKED so other processes don't get stuck waiting for the DELETE to finish, while each x# DELETE SQL takes very less amount of time to finish. I find it something like when vehicles pass through cross roads in a zipper fashion.

Which DELETE query is efficient by Time > CPU > Memory

2 Answers