I'm trying to delete in the order of 1M rows from a MySQL InnoDB table, with a schema similar to:

CREATE TABLE `relations` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `relation_type_id` int unsigned DEFAULT NULL,
  `asset_id1` int unsigned DEFAULT NULL,
  `asset_id2` int unsigned DEFAULT NULL,
  `active` smallint unsigned NOT NULL,
  `comments` longtext,
  PRIMARY KEY (`id`),
  UNIQUE KEY `relation_type_id` (`relation_type_id`,`asset_id1`,`asset_id2`),
  KEY `asset_id1` (`asset_id1`),
  KEY `asset_id2` (`asset_id2`),
  CONSTRAINT `relation_ibfk_1` FOREIGN KEY (`relation_type_id`) REFERENCES `relation_type` (`id`),
  CONSTRAINT `relation_ibfk_2` FOREIGN KEY (`asset_id1`) REFERENCES `asset` (`id`),
  CONSTRAINT `relation_ibfk_3` FOREIGN KEY (`asset_id2`) REFERENCES `asset` (`id`)

This table has about 250M rows.

I need to delete every record where asset_id1 belongs to a set of about 1M size. I'm using SQLAlchemy in python, SQL queries constructed look like

DELETE FROM relations where asset_id1 IN (a0, a1, ... a1_000_000) 

No assumptions on the ordering, even if I sort the input set, the relation rows id column may have a whole different ordering.

I'm taking 20-30 minutes to do this - am I missing any optimization techniques?

  • Do it in loops/batches. Queue up the full list of items to delete into a stage/temp table. Then loop through in batches of xxx thousand and do the deletes that way in batches. Even if it takes a while it will not completely block the table during the delete because you are doing it in batches/loops. Can even include a pause in each loop for 1 second or so to help ensure less blocking.
    – Brad
    Apr 3 at 17:17
  • 1
    i would create a temporary table and join it to find the wanted rows
    – nbk
    Apr 3 at 17:22
  • Thanks for the suggestions. Sadly, this is in the context of a longer transaction, so no implicit commits can be done, and the script does not have CREATE TEMPORARY TABLE privileges. Sorry for leaving that behind in the original problem statement. Apr 3 at 17:35


Your Answer

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

Browse other questions tagged or ask your own question.