30.2. SQL Statements for Table Maintenance

MySQL has several SQL statements for table maintenance: CHECK TABLE for integrity checking, REPAIR TABLE for repairs, ANALYZE TABLE for analysis, and OPTIMIZE TABLE for optimization. This section discusses these SQL statements and describes what they do. Section 30.3, "Client and Utility Programs for Table Maintenance," points out which MySQL client programs can be used to issue the statements.

Each statement, when issued, causes the server to perform the requested operation. The statement takes one or more table names and possibly optional keywords that modify the basic action to be performed. A table name can be unqualified to refer to a table in the current database, or qualified in db_name.table_name form to refer to a table in a specific database. For example, if world is the current database, the following statements are equivalent and instruct the server to check the world.City table:

 CHECK TABLE City; CHECK TABLE world.City; 

After performing the requested operation, the server returns information about the result of the operation to the client. The information takes the form of a result set with four columns. For example:

 mysql> OPTIMIZE TABLE City, CountryLanguage; +-----------------------+----------+----------+----------+ | Table                 | Op       | Msg_type | Msg_text | +-----------------------+----------+----------+----------+ | world.City            | optimize | status   | OK       | | world.CountryLanguage | optimize | status   | OK       | +-----------------------+----------+----------+----------+ 

Table indicates the table for which the operation was performed. Op names the operation (check, repair, analyze, or optimize). Msg_type provides an indicator of success or failure, and Msg_text provides extra information.

30.2.1. CHECK TABLE

The CHECK TABLE statement performs an integrity check on table structure and contents. It works for MyISAM and InnoDB tables. For MyISAM tables, it also updates the index statistics. If the table is a view, CHECK TABLE verifies the view definition.

If the output from CHECK TABLE indicates that a table has problems, the table should be repaired.

30.2.2. REPAIR TABLE

The REPAIR TABLE statement corrects problems in a table that has become corrupted. It works only for MyISAM tables.

You can tell the server to repair MyISAM tables automatically. See Section 30.5, "Enabling MyISAM Auto-Repair."

30.2.3. ANALYZE TABLE

The ANALYZE TABLE statement updates a table with information about the distribution of key values in the table. This information is used by the optimizer to make better choices about query execution plans. This statement works for MyISAM and InnoDB tables.

30.2.4. OPTIMIZE TABLE

The OPTIMIZE TABLE statement cleans up a MyISAM table by defragmenting it. This involves reclaiming unused space resulting from deletes and updates, and coalescing records that have become split and stored non-contiguously. OPTIMIZE TABLE also sorts the index pages if they are out of order and updates the index statistics.

OPTIMIZE TABLE also works for InnoDB tables, but maps to ALTER TABLE, which rebuilds the table. This updates index statistics and frees space in the clustered index.