How to optimize a delete query with a subselect? [closed]

Question

This query needs to delete over 17 million rows, from a table containing 20 million.

DELETE FROM statements 
where agreement_id IN (
    select id FROM agreements 
    WHERE created < DATE_SUB(CURDATE(), INTERVAL 6  MONTH)
);

DELETE FROM agreements 
WHERE created < DATE_SUB(CURDATE(), INTERVAL 6  MONTH)

It takes hours to run, am I missing something that could speed things up a bit?

The subselect by itself takes a few seconds, I don't understand why the delete takes so long.

Answer 1

Use Multiple-Table Syntax of DELETE statement.

DELETE s.*, a.*
FROM statements s, agreements a
WHERE s.agreement_id = a.id
  AND a.created < CURDATE() - INTERVAL 6 MONTH