0

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.

closed as too broad by Max Vernon, mustaccio, LowlyDBA, Mr.Brownstone, Evan Carroll Jan 14 at 20:56

Please edit the question to limit it to a specific problem with enough detail to identify an adequate answer. Avoid asking multiple distinct questions at once. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.

  • Please take a look at this advice – mustaccio Jan 10 at 20:16
  • 1
    I'm voting to close this question as off-topic because it lacks sufficient detail required to facilitate a comprehensive answer. Please see dba.meta.stackexchange.com/questions/2990/… for more details. Add the required details, and we'll likely re-open the question. – Max Vernon Jan 10 at 20:50
  • 1
    You'll probably need a larger innodb_log_file_size - see dev.mysql.com/doc/refman/5.7/en/innodb-redo-log.html. A large innodb_buffer_size is also recommended. – danblack Jan 11 at 0:44
  • 4
    You better move 3 million rows into a new table and delete the old one. – McNets Jan 11 at 9:33
  • 1
    Here are multiple techniques to deal with big deletes. Deleting more than a thousand rows at a time is costly because of the undo log. – Rick James Jan 12 at 2:06
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
  • I am not sure if it helps with the issue I had, but totally happy with the awesome tip you threw in. Karma is the least I could do. Thanks bud. – stefgosselin 40 mins ago

Not the answer you're looking for? Browse other questions tagged or ask your own question.