Help Center > > User Guide> MySQL Database Management> Table Management> Maintaining a Table

Maintaining a Table

Updated at: Mar 20, 2020 GMT+08:00

Scenarios

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.

Prerequisites

You have logged in to the DAS console.

Functions

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.

Procedure

The following uses optimizing table as an example to show how to maintain tables.

  1. In the navigation tree on the left, right-click the target table and choose Maintain Table > Optimize Table from the shortcut menu.
  2. In the displayed dialog box, click Yes.

Did you find this page helpful?

Submit successfully!

Thank you for your feedback. Your feedback helps make our documentation better.

Failed to submit the feedback. Please try again later.

Which of the following issues have you encountered?







Please complete at least one feedback item.

Content most length 200 character

Content is empty.

OK Cancel