Presently I'm doing mass removes using versions from the following query:
DELETE FROM t1 WHERE t1.key NOT IN (SELECT t2.key FROM t2)
I'm told that sub queries not fast enough in MySQL and also the ought to be enhanced. However I cannot look for a better example. Can you really perform a join and remove?
DELETE FROM t1 USING t1 JOIN t2 ON t1.key = t2.key LIMIT 1000;
And repeat until there is nothing left, this enables not obstructing table for any very long time.
UPD: but when you have to join on a single table this solution won't work.
UPD2: I overlooked the
NOT, this is actually the remedied query:
DELETE FROM t1 USING t1 LEFT JOIN t2 ON t1.key = t2.key WHERE t2.key IS NULL LIMIT 1000;