MYSQL has statements to analyze, optimize, check and repair database tables effectively. The statements which help in maintaining databases are given below:

Analyze

ANALYZE TABLE statement analyzes and stores the key distribution for a table. MYSQL uses the stored key distribution to decide the order in which tables should be joined when a join is performed on something other than a constant. The stored key distribution is also used when deciding which indexes to use for a specific table within a query. Syntax for ANALYZE TABLE is:

Syntax

ANALYZE TABLE `tblName`;

Example:

ANALYZE TABLE `tblstudents`;

This statement returns a result set with following columns:

  • Table - Saying the table name.
  • Op - Operation performed, i.e. ANALYZE.
  • Message Type - Info, note, warning, status...
  • Message text - An informational message.
NOTE:If the table is not changed since it is last analyzed, the table is not analyzed again.

Check Table

The CHECK TABLE statement checks table(s) for errors. It can perform if a table is referenced in a view which no longer exists. It works for InnoDB, MyISAM, and ARCHIVE tables. It is also valid in CSV tables. The syntax is:

Syntax

CHECK TABLE tblName [, tblName] ? [option];

Where,

  • Option can be FOR UPGRADE/QUICK/FAST/MEDIUM/EXTENDED/CHANGED.

Example:

CHECK TABLE `tblstudents`;

  • CHECK TABLES returns a result set with following columns:
  • Table - Saying the table name.
  • Op - Operation performed, i.e. CHECK.
  • Message Type - Info, note, warning, status...
  • Message text - An informational message.

Optimize table

The OPTIMIZE TABLE options reorganize the physical storage of table data and associated index data, and hence improve I/O efficiency and reduce storage space when accessing table. This syntax can be used, after doing substantial insert, update, and delete operations on table. Syntax is:

Syntax

OPTIMIZE TABLE tableName;

Example:

OPTIMIZE TABLE `tblstudents`;

  • OPTIMIZE TABLES returns a result set with following columns:
  • Table - Saying the table name.
  • Op - Operation performed, i.e. OPTIMIZE.
  • Message Type - Info, note, warning, status...
  • Message text - An informational message.

Repair Table

The REPAIR TABLE statement repairs a corrupted table. However, it does not guarantee the repair of all errors with this statement. The syntax is:

Syntax

REPAIR TABLE tableName;

Example:

REPAIR TABLE `tblstudents`;

REPAIR TABLES returns a result set with following columns:

  • Table - Saying the table name.
  • Op - Operation performed, i.e. REPAIR.
  • Message Type - Info, note, warning, status...
  • Message text - An informational message.