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