This post is a note for me to remember the common MySQL maintenance command. These MySQL maintenance command (check table, repair table, analyze table and optimize table) is very useful to ensure the MySQL database run smoothly and error free. Understanding these MySQL maintenance command will allow you to perform the right operation at the right time.
Here are the MySQL maintenance command simple brief:-
- CHECK TABLE
This command is mainly use to check table for errors. It support MyISAM and InnoDB
- REPAIR TABLE
If there is error shown during the CHECK TABLE process, then you will need to use REPAIR TABLE to fix the error.
- ANALYZE TABLE
This command analyze and store the key distribution for a table. During the operation, the table will be lock with READ LOCK. If your application perform a lot of DELETE and UPDATE operation, overtime you might need to perform ANALYZE TABLE to reorder the key distribution and improve the performance of the table.
- OPTIMIZE TABLE
If you perform large amount of DELETE operation, you might need to perform OPTIMIZE TABLE to reclaim your unused space and defragment the data file. This command also suitable if you perform heavy UPDATE modification on your table, it helps to improve the performance of the statement that uses the table.