Maintaining a Table
While working with the MySQL database, you do a lot of changes such as insert, update and delete data in the table that may cause table fragmentation. As a result, the performance of database server is deteriorated. You need to maintain tables periodically to improve the database performance.
You have logged in to the DAS console.
In the navigation tree on the left, right-click the target table and choose Maintain Table from the shortcut menu. The Maintain Table menu includes four types of operations: Optimize Table, Check Table, Repair Table, and Analyze Table.
- Optimize Table
MySQL provides you with the OPTIMIZE TABLE statement that allows you to optimize the table to avoid the fragmentation problem.
During the optimization, a read-only lock is added to the table. Therefore, you are advised to perform this operation during off-peak hours.
- Check Table
MySQL allows you to check whether an error occurs in database tables by using the CHECK TABLE statement. You can check the table with the following methods:
Check: Scan rows to verify that deleted links are valid. This also calculates a key checksum for the rows and verifies this with a calculated checksum for the keys.
Quick: Do not scan the rows to check for incorrect links.
Fast: Check only tables that have not been closed properly.
Changed: Check only tables that have been changed since the last check or that have not been closed properly.
Extended: Do a full key lookup for all keys for each row. This ensures that the table is 100% consistent, but takes a long time.
During the check, the target table is locked with a read lock.
- Repair Table
The MySQL database uses the REPAIR TABLE statement to repair possibly corrupted or incorrect tables. You can repair tables using the following three methods:
Repair: If you use the Repair option, REPAIR TABLE tries to repair the data files and index files.
Quick: If you use the Quick option, REPAIR TABLE tries to only repair the index files, but not the data files.
Extended: If you use the Extended option, REPAIR TABLE tries to create indexes row by row to repair data files and index files.
- Analyze Table
The MySQL database analyzes tables using the ANALYZE TABLE statement. During the analysis, a read-only lock is added to the tables. You cannot update in or insert data into the tables.
The following uses optimizing table as an example to show how to maintain tables.
- In the navigation tree on the left, right-click the target table and choose Maintain Table > Optimize Table from the shortcut menu.
- In the displayed dialog box, click Yes.