How Do I Check and Optimize Tables by Checking Top SQL?

Example Problem

A user found in the exported logs that it took more than 2s for a SELECT statement to query information of table test and the lock wait duration was long.

Suggestion

• Add indexes.
• Optimize tables

Procedure

1. Log in to the DAS 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, choose Intelligent O&M > Instance List.

   Alternatively, on the Overview page, click Go to Intelligent O&M.

5. In the upper right corner of the instance list, filter instances by engine, name, or IP address. Click Details.
   Figure 1 Intelligent O&M instance overview page
   

6. On the displayed page, click the SQL tab and then TOP SQL.
   Figure 2 TOP SQL
   

7. On the TOP SQL tab page, click View Details to view template information of SELECT and find the database in which SELECT was executed for longer than 2s.
   Figure 3 SQL statements
   

8. Log in to the target instance on the Development Tool page and choose Database Management. Select the database found in 7. Choose Tables in the navigation pane on the left, locate the table that you want to view, and click View in the Operation column. View the index length and row count in the table.
   Figure 4 Viewing table details
   

9. (Example) If there are few indexes, click Alter and add indexes. Return to the Tables tab and click Query SQL Statements.