What is the fastest way to delete millions of rows from a MySQL database?

Question

I ran out of space in my database, so I did a backup on old records. Now I have to clear these records out, and my reference column is 'date'.

I tried using the standard approach:

DELETE FROM table WHERE date >= '2017-01-01' AND date <= '2017-12-31'

But this is obviously taking too much time, because there are more than 7 million rows to delete. Is there a way to speed this up? I'm trying to divide in months and even smaller chunks, but after running the code for some time, I get disconnected from the server.

Thanks in advance.

EDIT:

CREATE TABLE table (

  id INT(11) NOT NULL AUTO_INCREMENT,
  date DATE DEFAULT NULL,
  # 18 more columns

  PRIMARY KEY (id)
)
ENGINE = INNODB,
AUTO_INCREMENT = 29616055,
AVG_ROW_LENGTH = 317,
CHARACTER SET utf8mb4,
COLLATE utf8mb4_general_ci;

Answer 1

If you have enough space, then create a temporary table and re-load the original table:

create table temp_t as
    select *
    from table
    where date >= '2018-01-01';

truncate table t;

insert into t
     select *
     from temp_t;

This saves all the logging overhead for the delete -- and that can be quite expensive.

Next, you need to learn about partitions. This makes the process much much simpler. You can just drop a partition, instead of deleting rows -- and there is no row-by-row logging for dropping a partition.

Answer 2

1. Create an empty copy of the table (keys and all).
2. INSERT...SELECT the data you want to preserve from the original into the copy.
3. Remove any foreign key constraints referencing the original table.
4. DROP the original table.
5. RENAME the copy to the original's name.
6. Recreate any foreign keys dropped in step 3.