Updated on 2023-12-13 GMT+08:00

Maintaining a Table

While working with MySQL databases, you do a lot of changes such as data insert, update, and deletion, which may cause table fragmentation. As a result, the database server performance is deteriorated. To handle this, periodic maintenance is required.

Functions

Table 1 Function description

Function

Description

Check

Allows you to check whether there are errors in database tables using the CHECK TABLE statement. You can check a table with any of the following methods: Check, Quick, Fast, Changed, and Extended.

The CHECK TABLE statement adds a read-only lock to the table.

  • Check: scans rows to verify that deleted links are valid. Alternatively, calculate a key checksum for the rows and verifies the validity using the obtained checksum.
  • Quick: checks only tables that have not been closed properly.
  • Fast: neither scans rows nor checks for incorrect links.
  • Changed: checks only the tables that have been changed since the last check and the tables that have not been closed properly.
  • Extended: searches for keywords in each row. This ensures that the table is 100% consistent, but takes a long time.

Repair

Allows you to use the REPAIR TABLE statement to repair possibly corrupted or incorrect tables. You can repair tables using any of the following three methods: Check, Quick, and Extended.

  • Check: a simple repair, which repairs data and index files.
  • Quick: the quickest repair, which repairs only index files, but not data files.
  • Extended: the slowest repair, which creates indexes row by row to repair data and index files.

Checking a Table

  1. Log in to the management console.
  2. Click in the upper left corner and select a region and project.
  3. Click in the upper left corner, and under Databases, click Data Admin Service.
  4. In the navigation pane on the left, choose Development Tool.

    You can also click Go to Development Tool on the overview page.

  5. Locate the DB instance that you want to log in to and click Log In in the Operation column.
  6. On the top menu bar, choose Database Management.
  7. Click Change on the right of the current database to switch to the database where you want to check a table.

    Figure 1 Switching to another database

  8. On the displayed Objects tab, choose Tables on the left.
  9. Locate the table you want to check and choose More > Maintain > Check in the Operation column.

    Figure 2 Checking a table

  10. Select a check method as required.

    You can check a table with any of the following methods: Check, Quick, Fast, Changed, and Extended.
    Figure 3 Selecting a check method

  11. In the displayed dialog box, click Yes.

Repairing a Table

  1. Log in to the management console.
  2. Click in the upper left corner and select a region and project.
  3. Click in the upper left corner, and under Databases, click Data Admin Service.
  4. In the navigation pane on the left, choose Development Tool.

    You can also click Go to Development Tool on the overview page.

  5. Locate the DB instance that you want to log in to and click Log In in the Operation column.
  6. On the top menu bar, choose Database Management.
  7. Click Change on the right of the current database to switch to the database where you want to maintain a table.

    Figure 4 Switching to another database

  8. On the displayed Objects tab, choose Tables on the left.
  9. Locate the table you want to repair and choose More > Maintain > Repair in the Operation column.

    Figure 5 Repairing a table

  10. Select a repair method as required.

    You can repair tables using any of the following three methods: Check, Quick, and Extended.
    Figure 6 Selecting a repair method

  11. In the displayed dialog box, click Yes.