Database Table Maintenance Statements in MySQL

MySQL provides a bunch of statements to allow you to maintain database table more efficiently. Those statements enable you to analyze, optimize, check, and repair the database table. Here you will learn them all together with examples.

Analyze table statement

Basically analyze table statement allow you to update cardinality of an index column. By updating cardinality, you can retrieve the database faster by utilizing all index features of database tables.

You will work with the employees and offices table in our sample database. Let’s follow the example bellow to understand more how analyze table statement works.

We can get the indexes information from employees table by executing the show index statement as follows:

SHOW INDEX FROM employees

MySQL gives you quite a lot of information about index in the employee table including cardinality; in this case the cardinality is 23.

Now we create a new index in the officeCode column to allow us to retrieve office and employee information by using join statement faster.

ALTER TABLE employees
ADD INDEX employee_offices (officeCode)

At this time the cardinality of the index is not updated, we can see this by performing the show index statement on employees table again.

SHOW INDEX FROM employees

The cardinality now is NULL.

So the cardinality of index is not updated automatically when index created and a new record is inserted to the table. In this case, we can use analyze table statement to make an update on cardinality of the index column. By executing the following query:

ANALYZE TABLE employees

And perform the show index statement on the table employees again you can see that the cardinality of indexed column employeeNumber and officeCode are updated.

Optimize table statement

While working with the database, you do a lot of changes such as insert, update and delete data in the database tables therefore it causes the physical of database table fragmented. So the performance of database server is also degraded. MySQL provides you optimize table statement to allow you to optimize the database table to avoid this problem by defragmenting the table in physical level. The optimize table statement is as follows:

OPTIMIZE TABLE table_name

With this optimize table statement, you should run it often with the tables which updated frequently.

Suppose you want to optimize the employees table to make it defragmented, you can perform the following query:

OPTIMIZE TABLE employees

Here is the output

Table                    Op        Msg_type  Msg_text
-----------------------  --------  --------  --------
classicmodels.employees  optimize  status    OK     

Check table statement

Something wrong can happen to the database server such as server turn off unexpectedly, error while writing data to the hard disk and so on… all of these situations could make the database operate incorrectly and for the worst thing it can be crashed. MySQL supports you to check database table by using check table statement. Here is the syntax:

CHECK TABLE table_name

The check table statements above check both table and correspondent indexes. For example, you can you check table statement to check the table employees as follows:

CHECK TABLE employees

And here is the output

Table                    Op      Msg_type  Msg_text
-----------------------  ------  --------  --------
classicmodels.employees  check   status    OK     

Check table statement only detect problems in a database table but it does not repair them. In order to do so you can you repair table statement.

Repair table statement

Repair table statement allows you to repairs some errors occurred in database tables. MySQL does not guarantee that this statement can repair all errors which your database may have. The repair table statement can be written as follows:

REPAIR TABLE table_name

Suppose you have some errors in the employees table and need to fix, you can use repair table statement by performing this query:

REPAIR TABLE employees

MySQL will return what it has done with the table and the table is repaired or not. Here is the output you always want to see in such cases:

Table                    Op      Msg_type  Msg_text
-----------------------  ------  --------  --------
classicmodels.employees  repair  status    OK