Tuesday, March 19, 2013

Commands to Analyze, Check, Optimize and Repair MySQL tables


There are some useful table maintenance commands that every MySQL user should aware about. These are the most basic and effective commands. You can use these commands to check the consistency of all the tables. If your table becomes corrupt then these commands are helpful in recovery of corrupt table. So let's discuss each command one by one.

Note: To run these commands you don't need to shutdown the MySQL Server.

Analyze Table: It is the table maintenance statement used to analyze and store the key distribution for a table. This command is used with both innodb and myisam tables. With innodb this command works fast while with myisam it takes longer time to scan the table. The syntax for the command is:
mysql>analyze table table_name;

Check Table: This command is used to check a particular table for error. It works with both innodb and myisam tables. If you are unable to access your table then you can use this command to check for error. The syntax for the command is:
mysql> check table table_name;
  • You can use quick, fast, medium, changed and extended options with check table command.
  • Quick option is used to scan the rows for incorrect links.
  • Fast option is used to check for those tables that are closed improperly.
  • Medium option is used to check deleted links to verify if they are valid or not.
  • Changed option is used to check for those tables that have been changed since the last check.
  • Final option is used to perform a full key lookup on all the rows.
Optimize Table: It is used to optimize and improve efficiency of I/O operation when accessing a table. This command is also used to reclaim the unused space and defrag the data file. The syntax for the command is:
mysql> optimize table table_name;

Repair Table: It is used to repair a corrupt table and works with myisam tables only. It is important to create a backup of your table before using this command as it may cause data loss. The syntax for the command is:
mysql> repair table table_name;





No comments:

Post a Comment