Aurora MySQL: Delete SQL Takes Forever to Finish

Question

I have an Aurora MySQL 5.7 running on a non-production test instance (r5.2xlarge). I have a "simple" delete where I am trying to execute and it has been running for 72 hours already! I like to know if anybody can give insights as to why the delete is taking a long time and how can I make it run faster.

The schema looks like this

       Table: t1
Create Table: CREATE TABLE `t1` (
  `id` char(36) NOT NULL DEFAULT '',
  `a_id` int(11) unsigned NOT NULL,
  `b_id` bigint(11) unsigned DEFAULT NULL,
  `created_at` datetime NOT NULL,
  `field_1` tinyint(1) unsigned NOT NULL,
  `field_2` tinyint(1) unsigned NOT NULL,
  `c_id` int(11) unsigned DEFAULT NULL,
  `field_3` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  `field_4` int(11) unsigned DEFAULT NULL,
  `field_5` int(11) unsigned DEFAULT NULL,
  `field_6` char(36) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `IDX_1` (`a_id`,`b_id`,`field_2`),
  KEY `IDX_2` (`b_id`),
  KEY `IDX_3` (`c_id`),
  KEY `IDX_4` (`a_id`,`created_at`),
  KEY `IDX_5` (`created_at`),
  KEY `IDX_6` (`a_id`,`field_1`,`field_2`),
  KEY `IDX_7` (`field_4`),
  KEY `IDX_8` (`field_5`),
  CONSTRAINT `ibfk1` FOREIGN KEY (`field_4`) REFERENCES `table_a` (`id`) ON DELETE CASCADE,
  CONSTRAINT `ibfk2` FOREIGN KEY (`field_5`) REFERENCES `table_b` (`id`) ON DELETE CASCADE,
  CONSTRAINT `ibfk3` FOREIGN KEY (`a_id`) REFERENCES `table_c` (`id`) ON DELETE CASCADE,
  CONSTRAINT `ibfk4` FOREIGN KEY (`b_id`) REFERENCES `table_d` (`id`) ON DELETE CASCADE,
  CONSTRAINT `ibfk5` FOREIGN KEY (`c_id`) REFERENCES `table_e` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.09 sec)

And the explain plan is this:

mysql> explain delete from t1 where created_at <= '2023-01-28 00:00:00'\G
*************************** 1. row ***************************
           id: 1
  select_type: DELETE
        table: t1
   partitions: NULL
         type: ALL
possible_keys: IDX_5
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 900000000
     filtered: 100.00
        Extra: Using where
1 row in set (0.08 sec)

Does anyone know what is going on and how can I make it faster? I wonder if it would help make the query run faster if I drop all those indexes and constraints.

Answer 1

Big deletes are slow. An "undo" row is created for each row being deleted -- just in case the system crashes.

If you are removing all the rows from a table, simply do

TRUNCATE TABLE name;

That will be virtually instantaneous, even for a large table. (It DROPs and reCREATEs the table.)

If you are deleting only a large number of rows, then here are several 'efficient' techniques: http://mysql.rjweb.org/doc.php/deletebig