Efficient way to delete a large amount of records from a big table

Question

I want to delete a large amount of records (~200K) from a large table (~500K records) in my MySql DB.

I want to make this call as efficient as possible because i dont want the DB to become "unresponsive" while executing the call. I need to delete records that are "older" than 10 days (according to created_at column), currently I use:

delete from table_name where created_at < DATE_SUB(CURDATE(),INTERVAL 10 DAY)

The table also have a primary key id if it helps.

Any thoughts?

Answer 1

I was faced with the same challenge. Having no primary key, but an index on the created_at field, it was not possible to delete the oldest 10Mio entries from a 100Mio row table (InnoDB). It constantly failed with lock overflows, due to the fact that the table is online and still receives INSERTS.

To solve the problem I created a stored procedure that tackled the job incrementally:

mysql> CREATE PROCEDURE delete_accesslog_incrementally()
-> MODIFIES SQL DATA
-> BEGIN
-> REPEAT
-> DELETE FROM tbl_name
-> WHERE created_at < DATE_SUB(CURDATE(),INTERVAL 10 day)
-> LIMIT 20000;
-> UNTIL ROW_COUNT() = 0 END REPEAT;
-> END $$

This still runs quite a while, but doesn't harm the concurrent INSERTS that are still hitting the table.

Answer 2

You could try this:

1) First find the first id value for the desired date:

select id from table_name where created_at = DATE_SUB(CURDATE(),INTERVAL 10 DAY) LIMIT 1;

2) Next delete in batches:

DELETE FROM table_name where id<"id_found_on_step_1" LIMIT 1000; 

On id_found_on_step_1 put the id value you found on step 1. You can try 10000 or lower than 1000. It depends how much time every delete command will take.

This DELETE should be faster, since its WHERE statement uses the primary key.

Answer 3

You have a WHERE condition, add an index on created_at field.