How to speed up delete from large tables – MySql (InnoDB engine)
Deleting data some times take very large amount of time when you are doing it on big tables have millions of rows and deletion of data is more important for them as growing tables will again create problems while querying it and updating it etc. Here are some techniques which i used/learnt while handling large table for purging data (InnoDB tables).
1. Delete in chunks: Deleting with limits works better than deleting without limits.
mysql>DELETE FROM table_name WHERE condition_for_deletion LIMIT 5000;
2. Drop indexes and re-build them after deletion:
Deleting indexes helps really when index size is also significant. While deleting it tries to update indexes and takes more time. Do not drop indexes on which you want to put the condition for deletion. Rebuilding indexes after the deletion is done will take less time comparatively.
3. Move data to new table:
mysql>create TABLE new_table LIKE old_table; mysql>INSERT INTO new_table SELECT * FROM old_table WHERE condition_for_non_delete_records; mysql>DROP TABLE old_table; mysql>RENAME TABLE new_table TO old_table;
Most Commented Posts
If you enjoyed this post, please consider to leave a comment or subscribe to the feed and get future articles delivered to your feed reader.
Comments
No comments yet.
Leave a comment