Table Maintenance Statements

In this section we are covering the topics like ANALYZE TABLE, BACKUP TABLE, CHECK TABLE, CHECKSUM TABLE, OPTIMIZE TABLE, REPAIR TABLE, RESTORE TABLE Syntax.

ANALYZE TABLE Syntax

The general syntax of ANALYZE TABLE statement is:
ANALYZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE tbl_name [, tbl_name] ...

This syntax is used to analyze and store the key distribution to a table. During the analyzing process table is locked with write lock to InnoDB and for MyISAM and BDB it is locked with a write lock.

MySQL uses this syntax for deciding the order in which tables are joined when we are performing a join on something other than a constant. For using ANALYZE TABLE syntax you must have the SELECT and INSERT privileges. Example :

mysql> ANALYZE TABLE Emp;
+--------------+---------+----------+----------+
| Table        | Op      | Msg_type | Msg_text |
+--------------+---------+----------+----------+
| employee.emp | analyze | status   | OK       |
+--------------+---------+----------+----------+
1 row in set (0.35 sec)

By using ANALYZE TABLE statement we retrieved the result set with following columns :

Column	Value
Table	The Table Name
Op	Always analyze
Msg_type	One of status, error, info or warning
Msg_text	The message

If you are not using optional keyword NO_WRITE_TO_BINGLOG then this statement is written to binary log that?s why this statement is used on a MySQL server and its acting as a replication master will be replicated by default to the replication slave.

BACKUP TABLE Syntax

The general syntax of BACKUP TABLE statement is :
BACKUP TABLE tbl_name [, tbl_name] ... TO '/path/to/backup/directory'

BACKUP TABLE Statement is used to copy the minimum number of table files required to restore the table to the backup directory. BACKUP TABLE statement works only to MyISAM tables. This statement is used to copy the .frm definition and .MYD data files and .MYI index file can rebuilt by .frm and .MYD files. For restoring the table we can use RESTORE TABLE. During this process each table is locked with read lock, one at time as they are being backed up.

By using BACKUP TABLE statement we retrieved the result set with following columns :

Column	Value
Table	The Table Name
Op	Always backup
Msg_type	One of status, error, info or warning
Msg_text	The message

Note - This statement is depricated.

CHECK TABLE Syntax

The general syntax of CHECK TABLE statement is :
CHECK TABLE tbl_name [, tbl_name] ... [option] ...

option = {FOR UPGRADE | QUICK | FAST | MEDIUM | EXTENDED | CHANGED}

CHECK TABLE statement is used for checking a table for errors. This statement works for InnoDB, MyISAM and ARCHIVE tables. This statements is used to check views also for problems. By using CHECK TABLE statement we retrieved the result set with following columns :

Column	Value
Table	The Table Name
Op	Always check
Msg_type	One of status, error, info or warning
Msg_text	The message

The option FOR UPGRADE is used check the named table are compatible or not with the current MySQL version. Server checks each table for determining whether there have been any incompatible changes in any data types of table or indexes when they was created. If any possible incompatibility available then server runs a full check on table and when it succeed then server marks the tables .frm file with MySQL current version.

Because of changing in storage format of data type or its sort order incompatibilities can occur. We have to avoid these changes but some times they are necessary to correct the problems which would be worse than an incompatibility between releases.

The rest of the check option are given in the following table and these are applied only for checking MyISAM TABLES and they are ignored for InnoDB tables and views.

Type	Meaning
QUICK	For checking the incorrect links it does not scan the rows.
FAST	It check only tables which have not been closed properly.
CHANGED	It check only tables which have been changed since the last check or not closed properly.
MEDIUM	For verifying the deleted links are valid or not it scan the rows. This also calculates a key checksum for the rows and verifies this with a calculated checksum for the keys.
EXTENDED	Do a full key lookup for all keys for each row. This ensures that the table is 100% consistent.

We can combine the check options also. The following example does a quick check on table for determining whether it was closed properly. Example :

mysql> CHECK TABLE Emp FAST QUICK;
+--------------+-------+----------+----------+
| Table        | Op    | Msg_type | Msg_text |
+--------------+-------+----------+----------+
| employee.emp | check | status   | OK       |
+--------------+-------+----------+----------+
1 row in set (0.21 sec)

CHECKSUM TABLE Syntax

The general syntax of CHECKSUM TABLE statement is :
CHECKSUM TABLE tbl_name [, tbl_name] ... [ QUICK | EXTENDED ]

CHECKSUM TABLE statement is used to report a table checksum. By using the QUICK option the live table checksum is reported when it is available else its reported NULL. By specifying CHECKSUM=1 table option we can enable the live checksum when we are creating the table. By using EXTENDED option the table reads row by row and checksum is calculated. It is very slow for large tables.

If any option is not specified then MySQL returns a live checksum if table storage engine supports it else it scans the table. Example :

mysql> CHECKSUM TABLE Emp QUICK;
+--------------+----------+
| Table        | Checksum |
+--------------+----------+
| employee.emp | NULL     |
+--------------+----------+
1 row in set (0.83 sec)

mysql> CHECKSUM TABLE Emp EXTENDED;
+--------------+-----------+
| Table        | Checksum  |
+--------------+-----------+
| employee.emp | 921501065 |
+--------------+-----------+
1 row in set (0.14 sec)

OPTIMIZE TABLE Syntax

The general syntax of OPTIMIZE TABLE statement is :
OPTIMIZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE tbl_name [, tbl_name] ...

OPTIMIZED TABLE statement is used when we have deleted a large part of table or when we have made much changes in a table. We can use this statement for reclaiming the unused space and for defragmenting the data file but for this statement you must have the SELECT and INSERT privileges. This statement works only for InnoDB, MyISAM and ARCHIVE tables.
It works as follows for MyISAM tables :

If the table has deleted or split rows, repair the table.
If the index pages are not sorted, sort them.
If the table's statistics are not up to date (and the repair could not be accomplished by sorting the index), update them.

This statement is mapped to ANALYZE TABLE for BDB tables and for InnoDB it is mapped to ALTER TABLE that rebuild the table for updating the index statistics. Example :

mysql> OPTIMIZE TABLE Emp2;
+---------------+----------+----------+----------+
| Table         | Op       | Msg_type | Msg_text |
+---------------+----------+----------+----------+
| employee.emp2 | optimize | status   | OK       |
+---------------+----------+----------+----------+
1 row in set (0.19 sec)

By using OPTIMIZE TABLE statement we retrieved the result set with following columns :

Column	Value
Table	The Table Name
Op	Always optimize
Msg_type	One of status, error, info, or warning
Msg_text	The message

If NO_WRITE_TO_BINLOG keyword is not used then the OPTIMIZE TABLE is written to binary log that?s why this statement is used on a MySQL server and its acting as a replication master will be replicated by default to the replication slave.

REPAIR TABLE Syntax

The general syntax of REPAIR TABLE statement is :
REPAIR [LOCAL | NO_WRITE_TO_BINLOG] TABLE tbl_name [, tbl_name] ... [QUICK] [EXTENDED] [USE_FRM]

REPAIR TABLE statement is used for repairing the possible corrupted table and it is worked only for MyISAM and ARCHIVE tables but for this statement you must have the SELECT and INSERT privileges. By using REPAIR TABLE statement we retrieved the result set with following columns :

Column	Value
Table	The Table Name
Op	Always repair
Msg_type	One of status, error, info, or warning
Msg_text	The message

This statement can produce many rows of information to each repaired table. If QUICK option is specified then this statement is tried to repair only the index tree and if EXTENDED is specified then MySQL creates the index row by row rather than creating a one index at a time with sorting. If .MYI index file is missing or its header is corrupted then we can use USE_FRM option. In this MySQL recreates the .MYI file by using the information from .frm file. Use this option when you cannot use regular REPAIR options. .MYI header have the important table metadata if we are using USE_FRM then they are lost. That?s why don?t use this option when the table is compressed because this information is also stored in the .MYI file. If NO_WRITE_TO_BINLOG keyword is not used then the REPAIR TABLE is written to binary log that?s why this statement is used on a MySQL server and its acting as a replication master will be replicated by default to the replication slave.

RESTORE TABLE Syntax

The general syntax of RESTORE TABLE statement is :
RESTORE TABLE tbl_name [, tbl_name] ... FROM '/path/to/backup/directory' 0

RESTORE TABLE statement is used to restore the table from backup that?s made by the BACKUP TABLE statement. We can not overwrite the existing table if we try to do this then an error occurs. This statement works only for MyISAM tables and they are not replicated from master to slave. Each table backup is consists of its format file .frm and data file .MYD and these files restores the restore operation after that we can use them to rebuild .MYI index file. By using RESTORE TABLE statement we retrieved the result set with following columns :

Column	Value
Table	The Table Name
Op	Always restore
Msg_type	One of status, error, info, or warning
Msg_text	The message

Table Maintenance Statements