I recently had an issue with mysql database and the database has grown too large about 200k records and i was needing to delete all junk and needless data of approx 50k rows in my table. As soon as i hit delete sql query using WHERE clause, it almost took about 1 hr and the task was still running and i noticed that the query was able to do delete of only 10k rows which to me is amazingly slow.
Running this DELETE query had locked all tables causing the site to go down and users see “Too many sql connections” and the server load was almost at 35/1.0
Always Run DELETE for large rows using LIMIT
I had to resort to killing the mysql process and restart the mysql server to bring back the site online. So never use delete directly on large datasets without using LIMIT because it puts enormous load on your server. You can set limit to either 100 rows or 1000 rows not more than that.
DELETE from <tbldata> where date <= ‘2009’ LIMIT 500
I did quite a research on using delete and it turns out that delete is amazingly slow on databases and the only way is to run delete on small chunks of data. You can write a php script running as cron which execute delete when the server is idle.
Keep in mind that running DELETE will LOCK your tables. So perform this operation only when the server is idle.
So whats the solution?
The only way you can clean up your table is using the following methods.
1. Apply DELETE on small chunks of rows usually by limiting to max 10000 rows. Do this only your server is IDLE.
2. Make sure you create INDEX in your table on the auto_increment primary key and this way delete can speed up things enormously. I created a index on certain fields in the table and ran DELETE with LIMIT 10000 and it took about 1.2 second to perform the operation which is just fine i guess.
2. Another method is copy the data you want to save to a new table, and then use TRUNCATE on the old table and then copy back. This way your users will see less down time and this method is the fastest although its bit indirect.
