» MySQL Adminsitration » Maintaining Database Tables

Maintaining Database Tables

Summary: MySQL provides several commands to allow you to maintain database table more efficiently. Those commands enable you to analyze, optimize, check, and repair the database tables. In this tutorial, you will learn those MySQL command to maintain database tables.

Analyze table statement

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

You will work with the employees and offices tables in our sample database. Let’s follow the examples below 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 faster by using join clause.

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 trigger an update on cardinality of the index column by executing the following query:

ANALYZE TABLE employees

If you 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. As a result, the performance of database server is  degraded. MySQL provides you optimize table statement to allow you to optimize the database table to avoid this problem by defragmenting the table at 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 in the worst case it can be crashed. MySQL supports you to check database tables by using check table statement. Here is the syntax of check table statement:

CHECK TABLE table_name

The check table statement checks 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 detects 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 repair 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
Search