The CLZ Concepts Blog

How to do fast SQL Deletes

Posted by Linus Thu, 08 May 2008 18:35:00 GMT

This week, I’ll change it up and make a technical post. We ran into this problem when we tried to delete 20 million rows without bringing down our live site. A simple ‘delete from FOO where id < 20000000’ would have taken too long because the database would have to go through those rows, recreate the indices, and do all the internal SQL cleanup. Given these problems we had to do it a better way. The general idea is to:

1. create a temp table
2. insert the rows we want into the temp table
3. recreate the indices
4. switch over the tables.

The actual SQL statements is:

CREATE TABLE foo_temp (
  `id` int(11) NOT NULL auto_increment,
  `my_data` text NOT NULL,
);

INSERT INTO foo_temp (SELECT * FROM foo WHERE id > 20000000);

CREATE INDEX `foo_index` ON foo_temp(`my_data`);

DROP TABLE foo;

RENAME TABLE foo_temp TO foo;