Slow DELETE in MySQL with a subquery

Question

I am running the following DELETE SQL on a MySQL database on a MyISAM/utf8 table. The SQL is called in a loop, once time per Users (in this case):-

DELETE FROM regulatory WHERE documentid  IN
( 
    SELECT * FROM
    (
        SELECT t1.`DocumentID` FROM regulatory T1, regulatory T2
        WHERE (t1.`group_ID`=v_user_id AND t2.`group_ID`=v_user_id AND
        TRIM(t1.`title`)=TRIM(t2.`title`) AND  TRIM(t1.`spare1`='red.gif')
        AND (t1.`docref`=v_temp) AND t1.`DocumentID`>t2.`DocumentID`) 
    ) DocumentID
) ;

But it is taking over a minute per User - how can I optimize this at all so it takes far less time each user?

score 1 · Answer 1 · answered Jul 11 '14 at 08:19

Try removing the nesting levels, mysql can not delete from a table that's referenced as a child, instead there's a propriety trick like:

    DELETE t1.* 
    FROM regulatory T1
    JOIN regulatory T2 
        ON t1.group_ID=v_user_id  
       AND t2.group_ID=v_user_id 
       AND TRIM(t1.title)=TRIM(t2.title) 
       AND TRIM(t1.spare1='red.gif') 
       AND t1.docref=v_temp
       AND t1.DocumentID>t2.DocumentID

Also, like @Mihal suggested try removing all function calls like trim from predicates, they are expensive.

Erwin Smout · Answer 2 · 2014-07-11T14:21:25.313

It may be due to unnecessary full materialization of the self-join.

The only reason you are using T2 is to check that there is a lower DocumentID than the one you've gotten from T1. But if there are 100 such, the join will have 100 rows for the same v_user_id/T1.DocumentID. That's not necessary. Try to rewrite as

... AND EXISTS ( SELECT * FROM regulatory T2 WHERE ...)

EDIT

That is, from the innermost SELECT,

remove everything that references T2 :

SELECT t1.DocumentID FROM regulatory T1~~, regulatory T2~~
WHERE (t1.group_ID=v_user_id ~~AND t2.group_ID=v_user_id AND TRIM(t1.title)=TRIM(t2.title)~~
AND TRIM(t1.spare1='red.gif') AND (t1.docref=v_temp) ~~AND t1.DocumentID>t2.DocumentID)~~

and add the existential predicate :

AND EXISTS ( SELECT * FROM regulatory T2 WHERE ...)

replacing those ellipsis with all the WHERE clauses that were removed just prior :

... WHERE T2.group_ID=v_user_id AND TRIM(T2.title) = TRIM(T1.title) and T2.DocumentID < T1. ...

The main restricting predicate in the WHERE clauses here are on GROUP_ID and DocumentID. Check that there are indexes on these columns.

score 1 · Answer 3 · edited Apr 13 '17 at 12:42

You are using a selfie-selfie (SELF JOIN within a SELF JOIN). Queries that do self joins in the middle of UPDATEs and DELETEs can have unpredictable results because of two possibilities:

rows not to be deleted when it should have been deleted
rows deleted that were not meant to be deleted

I wrote about this quirky behavior 3 years ago : Problem with MySQL subquery

You should decompose the DELETE into separate steps

CREATE TABLE DocsToDelete
    SELECT DocumentID FROM
    (
        SELECT t1.`DocumentID` FROM regulatory T1, regulatory T2
        WHERE (t1.`group_ID`=v_user_id AND t2.`group_ID`=v_user_id AND
        TRIM(t1.`title`)=TRIM(t2.`title`) AND  TRIM(t1.`spare1`='red.gif')
        AND (t1.`docref`=v_temp) AND t1.`DocumentID`>t2.`DocumentID`) 
    ) DocumentIDSubquery;
DELETE B.* FROM DocsToDelete A LEFT JOIN regulatory B USING (DocumentID);
DROP TABLE DocsToDelete;

Give it as Try !!!

Slow DELETE in MySQL with a subquery

3 Answers3

Linked