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. Choose Intelligent O&M > Instance List.
3. On the Instance Overview page, locate the instance you want to view and click Details.
   Figure 1 Intelligent O&M instance overview page
   

4. On the displayed page, choose SQL > SQL Explorer.
5. Click the TOP SQL tab.
   Figure 2 TOP SQL
   

6. In the template list, locate the required SELECT template and click Details in the Operation column.
7. In the SQL statement list, locate database db_test whose template execution took over 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.
   Figure 5 SQL Window