Several statements to maintain tables in MySQL

MySQL provides several statements for maintaining tables, ANALYZE TABLE, OPTIMIZE TABLE, CHECK TABLE and REPAIR TABLE.

MySQL provides several statements for maintaining tables: ANALYZE TABLE, OPTIMIZE TABLE, CHECK TABLE and REPAIR TABLE.

It is a good habit to maintain database tables regularly. It is very helpful to improve the performance of the database.

MySQL provides several statements for maintaining database tables:

  • ANALYZE TABLE: Analysis table
  • OPTIMIZE TABLE: Optimize table
  • CHECK TABLE: Check table
  • REPAIR TABLE: Repair table

Analysis Table

MySQL provides ANALYZE TABLE statement to analyzes tables, which is used to analyze and store the distribution of keys. The result of analyzing the table will allow the system to obtain accurate statistical information, so that MySQL can generate the correct execution plan for statements.

Typically, after a large number of INSERT, UPDATE, or DELETE are performed in a table, the key distribution may be inaccurate. If the key distribution is inaccurate, the query optimizer may choose the wrong query execution plan, which can cause serious performance problems.

If you feel that the actual execution plan is not the expected execution plan, executing the analysis table once may solve the problem.

The following statement analyzes the actor table from Sakila sample database ::

ANALYZE TABLE sakila.user;

+-------------+---------+----------+----------+
| Table       | Op      | Msg_type | Msg_text |
+-------------+---------+----------+----------+
| sakila.user | analyze | status   | OK       |
+-------------+---------+----------+----------+

You can also analyze multiple tables in one statement, for example:

ANALYZE TABLE sakila.actor, sakila.film;

+--------------+---------+----------+----------+
| Table        | Op      | Msg_type | Msg_text |
+--------------+---------+----------+----------+
| sakila.actor | analyze | status   | OK       |
| sakila.film  | analyze | status   | OK       |
+--------------+---------+----------+----------+

Optimize table

MySQL provides the OPTIMIZE TABLE statement to optimize the table, and its main function is to eliminate the space waste caused by update or delete.

Typically, after a large number of UPDATE or DELETE operations in a table, the physical storage of the table may become fragmented. Invariably result in the performance of the database server degraded.

The following statement optimizes the film table from Sakila sample database:

OPTIMIZE TABLE sakila.actor;

+-------------+----------+----------+----------+
| Table       | Op       | Msg_type | Msg_text |
+-------------+----------+----------+----------+
| sakila.film | optimize | status   | OK       |
+-------------+----------+----------+----------+

If a table does not support optimization, MySQL will give a note: Table does not support optimize, doing recreate + analyze instead.

Check Table

Some errors may occur with the database server, such as an unexpected server shutdown, an error writing data to the hard disk, etc. These conditions can cause the database to behave incorrectly and, in the worst case, crash.

MySQL allows you to check the integrity of database tables using the CHECK TABLE statement.

The following statement checks the actor table:

CHECK TABLE sakila.actor;

+--------------+-------+----------+----------+
| Table        | Op    | Msg_type | Msg_text |
+--------------+-------+----------+----------+
| sakila.actor | check | status   | OK       |
+--------------+-------+----------+----------+

The CHECK TABLE statement only detect problems in database tables, but do not fix them. To repair a table, use the REPAIR TABLE statement.

Repair table

The REPAIR TABLE statement allow you to fix some errors that occur in database tables. MySQL does not guarantee that the REPAIR TABLE statement can repair all errors.

Only the MyISAM engine supports REPAIR TABLE. Otherwise, a prompt will be given: The storage engine for the table doesn’t support repair.

The following statement checks the actor tables :

REPAIR TABLE sakila.actor;

+--------------+--------+----------+---------------------------------------------------------+
| Table        | Op     | Msg_type | Msg_text                                                |
+--------------+--------+----------+---------------------------------------------------------+
| sakila.actor | repair | note     | The storage engine for the table doesn't support repair |
+--------------+--------+----------+---------------------------------------------------------+

Here, MySQL returned a hint because mysql data engine is InnoDB.

Summarize

MySQL provides several statements for maintaining database tables ANALYZE TABLE, OPTIMIZE TABLE, CHECK TABLE and REPAIR TABLE for analyzing tables, optimizing tables, checking tables, and repairing tables, respectively.