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.
- 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 using the following five methods:
Check: Scan rows to verify that the deleted link is 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: Look up for all keys for each row. This ensures that the table is 100% consistent, but this takes a long time.
During the check, a read-only lock is added to the table.
- Repair Table
The MySQL database uses the REPAIR TABLE statement to repair damaged 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
MySQL allows you to analyze the database tables by using the ANALYZE TABLE statement. During the analysis, a read-only lock is added to the table so you cannot update or insert data in the table.
The following uses optimizing table as an example to show how to maintain tables.
- On the top menu bar, choose Database Management.
- Locate the target table and choose More > Maintain.
- In the displayed dialog box, click Yes.