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.