Oracle 8i | Oracle 9i | Oracle 10g | Oracle 11g | Oracle 12c | Miscellaneous | PL/SQL | SQL | Oracle RAC | Oracle Apps | Linux

MySQL : OPTIMIZE TABLE, ANALYZE TABLE, CHECK TABLE and REPAIR TABLE Commands

This article describes some common table maintenance operations in MySQL. The focus is very much on the InnoDB storage engine, but the documentation links provide more information, including how the functionality related to the MyISAM storage engine.

OPTIMIZE TABLE

The basic syntax for this command is as follows.

OPTIMIZE TABLE myTable1, myTable2;
OPTIMIZE NO_WRITE_TO_BINLOG TABLE myTable1, myTable2;

The NO_WRITE_TO_BINLOG option can be used if you don't want the operation to be pushed to replication slaves.

The following is a quote from the documentation for this command.

"Reorganizes the physical storage of table data and associated index data, to reduce storage space and improve I/O efficiency when accessing the table. The exact changes made to each table depend on the storage engine used by that table." MySQL 5.7 Docs

This is an over simplistic view of the operation. In the case of a table with a primary key index, the optimize operation will compact the data and may improve performance. For tables with secondary indexes, the optimize operation may adversely affect performance by causing index fragmentation in the secondary indexes. You can read more about this here.

There is no hard and fast rule for when to issue an OPTIMIZE TABLE command, although some would say never! If you do decide it is necessary, it may be sensible to drop and secondary indexes, perform the OPTIMIZE TABLE command, then recreate the secondary indexes.

ANALYZE TABLE

The basic syntax for this command is as follows.

ANALYZE TABLE myTable1, myTable2;
ANALYZE NO_WRITE_TO_BINLOG TABLE myTable1, myTable2;

The NO_WRITE_TO_BINLOG option can be used if you don't want the operation to be pushed to replication slaves.

The ANLYZE TABLE command gathers statistics, allowing MySQL to make better decisions when deciding on join operations. On heavily modified tables that are displaying performance issues, analyzing may give a performance benefit by making sure the statistics are representative of the data distribution in the tables and indexes.

If a table has not changed since it was last analyzed, the operation will be skipped.

CHECK TABLE

The basic syntax for this command is as follows.

CHECK TABLE myTable1, myTable2;
CHECK TABLE myTable1, myTable2 QUICK;

The CHECK TABLE command check for errors in tables or views. Depending on the nature of the error and the version of MySQL, the result may be anything from shutting down the service, to marking a table or index as corrupt, or just writing a message to the error log (/var/log/mysqld.log).

If a table has not changed since it was last checked, the operation will be skipped.

REPAIR TABLE

The basic syntax for this command is as follows.

REPAIR TABLE myTable1, myTable2;
REPAIR NO_WRITE_TO_BINLOG TABLE myTable1, myTable2;

The NO_WRITE_TO_BINLOG option can be used if you don't want the operation to be pushed to replication slaves.

The REPAIR TABLE command attempts to fix corrupt tables. If things do not go to plan it can leave the tables in an inconsistent state, so make sure you have a backup before running the command.

For more information see:

Hope this helps. Regards Tim...

Back to the Top.