Basically, we have a table with the following structure:

    `reqid` BIGINT(20) NOT NULL,
    `from` BIGINT(20) NOT NULL,
    `to` BIGINT(20) NOT NULL,
    `rdate` DATETIME NOT NULL,
    `state` ENUM('sent','approved','accepted','rejected') NOT NULL,
    `type` INT(11) NOT NULL,
    `subtype` INT(11) NOT NULL,
    `clusterid` INT(11) NOT NULL,
    PRIMARY KEY (`id`),
    INDEX `to_state_clusterid` (`to`, `state`, `clusterid`),
    INDEX `from_state_clusterid` (`from`, `state`, `clusterid`),
    INDEX `reqid_to` (`reqid`, `to`),
    INDEX `from_rdate_type` (`from`, `rdate`, `type`),
    INDEX `type` (`type`),
    INDEX `from_state_type_clusterid` (`from`, `state`, `type`, `clusterid`),
    INDEX `to_state_type_clusterid` (`to`, `state`, `type`, `clusterid`),
    INDEX `state` (`state`),
    INDEX `from_to_state_type_clusterid` (`from`, `to`, `state`, `type`, `clusterid`),
    INDEX `from_rdate_type_clusterid` (`from`, `rdate`, `type`, `clusterid`)

It's currently around ~250M rows and we'd like to delete 200M+ of them. This is a production database and this table in particular gets dozens of INSERT/UPDATE/SELECT queries every second.

Finally, I don't know if it changes anything, but all SELECTs to this table have USE INDEX statements in them.

What would be the best way to do this with minimal impact to our application?

EDIT: There are 2 DELETE statements to be executed:

DELETE FROM `reqs` WHERE `state` = 'accepted' or `state` = 'rejected';
DELETE FROM `reqs` WHERE `rdate` < '2013-01-01 00:00:00';

These queries are expected to delete ~70M and ~130M rows respectively.

  • What would be the DELETE statement? Commented Sep 11, 2013 at 7:48
  • @ypercube I have updated the question. Commented Sep 11, 2013 at 8:12
  • If you have a time slot without activities on this table. Maybe,it will be quickier to rebuild the table/indexes without the uneeded rows. Commented Sep 11, 2013 at 10:23
  • Sadly, no. This table is very active 24/7. Commented Sep 11, 2013 at 10:36

1 Answer 1


Any data change action of this size if executed at once will cause a significant logging overhead and will require a lot of additional log space. That in turn will slow down the execution causing an even bigger impact on the rest of the system.

The best way to handle this is to break the delete down into smaller chunks. If you delete between 1000 and 10000 rows at a time and then wait for a few seconds the impact on other transactions should be small. Make sure each delete runs in a separate transaction so that all locks are released during the wait time. However, keep in mind that this will stretch out the delete over several hours or even days.

Also, even though it might sound counter intuitive, make sure there is an index supporting each delete so that MySQL does not have to execute a full table scan each time.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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