Slow DELETE in MySQL with a subquery 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`&gt;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?
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&gt;t2.DocumentID


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