Summary: in this tutorial, we will introduce you to some very useful statements that allow you to maintain database tables in MySQL.
MySQL provides several useful statements that allow you to maintain database tables effectively. Those statements enable you to analyze, optimize, check, and repair database tables.
Analyze table statement
MySQL query optimizer is an important component of the MySQL server that creates an optimal query execution plan for a query. For a particular query, the query optimizer uses the stored key distribution and other factors to decide the order in which tables should be joined when you performing the join, and which index should be used for a specific table.
However, 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.
To solve this problem, you can run the
ANALYZE TABLE statement for the table e.g., the following statement analyzes the
payments table in the sample database.
ANALYZE TABLE payments;
If there is no change to the table since the ANALYZE TABLE statement ran, MySQL will not analyze the table again. If you run the above statement again:
ANALYZE TABLE payments;
It is saying that the table is already up to date.
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:
OPTIMIZE TABLE table_name;
It is recommended that you execute this statement for the tables that are updated frequently. For example, if you want to optimize the orders table to defragment it, you can perform the following statement:
OPTIMIZE TABLE orders;
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 the
CHECK TABLE statement. The following illustrates the syntax of the
CHECK TABLE statement:
CHECK TABLE table_name;
CHECK TABLE statement checks both table and its indexes. For example, you can use the
CHECK TABLE statement to check the
orders table as follows:
CHECK TABLE orders;
CHECK TABLE statement only detects problems in a database table but it does not repair them. To repair the table, you use the
REPAIR TABLE statement.
Repair table statement
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:
REPAIR TABLE table_name;
Suppose there are some errors in the
orders table and you need to fix them, you can use the
REPAIR TABLE statement as the following query:
REPAIR TABLE employees;
MySQL returns what it has done to the table and shows you whether the table was repaired or not.
In this tutorial, you have learned some very handy statements to maintain database tables in MySQL.