Pirx un[blog]ged

Mike is reading four blogs. This is none of them.

Delete records in large chunks

I'm not a database administrator and use SQL databases from a programmers perspective. That is I'm not interested in learning the subtleties of every SQL dialect. But when dealing with large tables in MySQL certain peculiarities of this database have to be kept in mind. And that is, you can't delete a large amount of records from a large table without to run the database into the ground. First it takes too much time. And second after a while you get errors like Lock count exceeded or Connection timeout, etc.

The MySQL keyword LIMIT comes to help:

DELETE FROM production.data where (ts < CURRENT_DATE() - INTERVAL 40 DAY) LIMIT 50000;

This is an example to delete all records are older then 40 days but not more then 50000 at once. Simply repeat this command if more then 50000 records affected. There is surely an elegant way to write a stored procedure. But as I said, I'm not a DBA.


comments powered by Disqus