Maintaining MySQL Database Tables.

MySQL provides several useful statement that allows you to maintain database tables effectively. Those statements enable you to analyze, optimize, check, and repair database tables.

Analyze Table:

    Analyze table command analyzes & stores the key distribution for the table.

The key distributions can be sometimes inaccurate e.g., after you have done a lot of data changes in the table including insert, delete or update. If the key distribution is not accurate, the query optimizer may pick a bad query execution plan that may cause a severe performance issue.

We can run the ANALYZE TABLE statement for the table.

mysql> show databases;

+———————–+

| Database |

+———————–+

| information_schema |

| monkey |

| mysql |

| performance_schema |

| sakila |

| test |

| world |

+————————+

7 rows in set (0.26 sec)

mysql> use world; 

Database changed

mysql> show tables;

+—————–+

| Tables_in_world |

+—————–+

| city |

| country |

| countrylanguage |

+—————–+

3 rows in set (0.03 sec)

mysql> analyze table city;

+————+———+———-+———-+

| Table | Op | Msg_type | Msg_text |

+————+———+———-+———-+

| world.city | analyze | status | OK |

+————+———+———-+———-+

1 row in set (0.41 sec)

Optimize table statement:

While working with the database, you do a lot of changes such as insert,update and delete data in the table that may cause the physical storage of the table fragmented. As a result, the performance of database server is degraded.

MySQL provides you with a statement that allows you to optimize the table to avoid this defragmenting problem. The following illustrates how to optimize a table.

mysql> optimize table city;

+————+———-+———-+———-+

 | Table | Op | Msg_type | Msg_text |

 +————+———-+———-+———-+

| world.city | optimize | status | OK |

 +————+———-+———-+———-+

 1 row in set (0.10 sec)

 Check table statement:

 Something wrong can happen to the database server e.g., the server was shutdown unexpectedly, error while writing data to the hard disk, etc. These situations could make the database operate incorrectly and in the worst case it can be crashed.

 MySQL allows you to check the integrity of database tables by using theCHECK TABLE statement. The following illustrates the syntax of the CHECK TABLE statement:

 mysql> check table country;

+—————+——-+———-+———-+

| Table | Op | Msg_type | Msg_text |

+—————+——-+———-+———-+

| world.country | check | status | OK |

+—————+——-+———-+———-+

1 row in set (0.20 sec)

Repair table statement:

The REPAIR TABLE statement allows you to repair some errors occurred in database tables. MySQL does not guarantee that the REPAIR TABLE statement can repair all errors that the tables may have. The following is the syntax of the REPAIR TABLE statement:

mysql> repair table country;

+—————+——–+———-+———-+

| Table | Op | Msg_type | Msg_text |

+—————+——–+———-+———-+

| world.country | repair | status | OK |

+—————+——–+———-+———-+