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;

What have you tried to debug the problem? Is there any index set? — Nico Haase, Feb 18 '19 at 18:21
Is the table's Engine InnoDB or MyISAM? (or to answer my and Nico's question, add the table's CREATE statement your question). — Uueerdo, Feb 18 '19 at 18:23
@Uueerdo, InnoDB! No approaches to debbuging yet, but this table is populated with daily data (for reporting only) and it doesn't point to any other tables. — Vinícius Alcântara, Feb 18 '19 at 18:27
`ALTER TABLE \`table\` ADD INDEX date_index (\`date\`);` What is your column `date` type is? — Alex, Feb 18 '19 at 18:27
`WHERE \`date\` >= STR_TO_DATE('2017-01-01', '%Y-%m-%d') AND date <= STR_TO_DATE('2017-12-31', '%Y-%m-%d')` just to be sure that is same type — Alex, Feb 18 '19 at 18:33
Possible duplicate of [Deleting millions of rows in MySQL](https://stackoverflow.com/questions/1318972/deleting-millions-of-rows-in-mysql) — Alex, Feb 18 '19 at 18:43
Depending on how your server has innodb configured, i.e. not file per table, deleting the table may not free up any disk space (just space within the innodb file for new data). — Uueerdo, Feb 18 '19 at 19:04

score 13 · Accepted Answer · answered Feb 18 '19 at 18:55

13

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.

answered Feb 18 '19 at 18:55

Gordon Linoff

1,242,037
58
646
786

1

Great. It worked like charm. I have tested this answer on ~1100 million rows and it worked as expected. Thanks for the sharing this answer. – Mayur Patel Jan 30 '20 at 06:42
This is a great alternative to delete, I wonder how `create table` can be faster. – Joel Mar 04 '20 at 00:00
@joel . . . Less logging and minimal impact of locking. – Gordon Linoff Mar 04 '20 at 02:11

score 1 · Answer 2 · answered Feb 18 '19 at 19:10

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

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

2 Answers2