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.

This feature is available only in regions CN-Hong Kong, CN East-Shanghai1, AP-Singapore, and LA-Sao Paulo1.

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.
4. On the displayed page, choose SQL > SQL Explorer.
5. Click the TOP SQL tab.
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.
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.
9. (Example) If there are few indexes, click Alter and add indexes. Return to the Tables tab and click Query SQL Statements.