Home / Speed Up Queries / Question No: 28

This query works but is there any way of making it more elegant or speeding it up?

DELETE from bm_KW USING bm_KW, bmjn_KW WHERE bm_KW.KeywordID 
IN(SELECT bmjn_KW.KeywordID FROM bmjn_KW 
WHERE bmjn_KW.ItemID = '1016' ) AND bm_KW.KeywordID 
NOT IN (SELECT bmjn_KW.KeywordID FROM bmjn_KW 
WHERE bmjn_KW.ItemID != '1016');

Its purpose is to delete only the keywords which are unique to the item being deleted, "1016" in this case.

Try the below, may it seems more speedy.

DELETE bm_KW FROM bm_KW INNER JOIN 
    (SELECT kw2.KeywordID FROM bmjn_KW kw1 
      INNER JOIN bmjn_KW kw2 ON kw1.KeywordID = kw2.KeywordID 
      AND kw2.ItemID != '1016' WHERE kw1.ItemID = '1016'
    ) AS keywords
ON bm_KW.KeywordID = keywords.KeywordID;