Optimize SQL Queries with Visual Execution Plans - Database Autonomy Service

Database Autonomy Service (DAS) automatically analyzes SQL statements and returns optimization suggestions with expected performance gains — so you can decide whether to apply them. Use this feature for slow SQL troubleshooting, pre-release code reviews, and routine self-checks.

Prerequisites

Before you begin, make sure that:

The database engine is one of the following:
- RDS for MySQL — Basic and Cluster editions are not supported
- MyBase MySQL
- PolarDB for MySQL — single-node clusters (formerly standalone instances) are not supported
- PolarDB-X 2.0 — instances with major version 5.4.13 and minor version in the range [16415631, 16504348] are not supported. To check your version, see View and upgrade an instance version
- MongoDB
The instance is connected to DAS. See Introduction to instance connection
The instance's accessed state is Normal Access

Limitations

SQL statements that use X-Engine tables cannot be diagnosed or optimized.
In PolarDB-X, when a SQL statement runs using a prepared statement, the slow query log records the statement template (for example, select * from test where a = ? and b = ?) and the bound parameters (for example, params: [1, 2]) separately. Because this format is not a directly executable SQL statement, features that rely on the original statement for analysis or optimization may be limited.

Optimize SQL from the Slow Log Analysis page

Important

SQL optimization on the Slow Log Analysis page is supported only for RDS for MySQL 5.6, 5.7, and 8.0 and PolarDB for MySQL 5.6, 5.7, and 8.0.

Log on to the DAS console.
In the left navigation pane, choose Intelligent O&M Center > Instance Monitoring.
Find the target instance and click the instance ID to open the instance details page.
In the left navigation pane, choose Request Analysis > Slow Logs.
On the Slow Log Analysis page, find the SQL statement to optimize:
- On the Slow Log Statistics tab, find the target SQL template and click Optimize in the Actions column.
- On the Slow Log Details tab, find the target SQL statement and click Optimize in the Actions column.
In the dialog box that appears, click Create Plan.

Select an execution node type:

Node type	Description	When to use
Standby Node (Default)	Secondary node of the current instance	Routine query analysis
Event Node	Business node where the SQL statement was executed	Troubleshooting and optimization

Click Confirm Creation.

DAS generates a visual execution plan for the selected SQL statement.

Read the visual execution plan

The visual execution plan shows the execution status at the current time — it is not a historical record.

Execution flows from bottom to top and from left to right. The node at the bottom is the query's starting point; data flows upward through layers until the final result is output.

For complex execution graphs, use the scaling tool to adjust the view, or click the reset button to return to the initial view.

Node colors

Node color indicates access efficiency:

Color	Efficiency	Access methods
Green	Efficient	`system`, `const`, `eq_ref`, `ref`, `ref_or_null`, `index_merge`
Yellow	Medium	`fulltext`, `unique_subquery`, `index_subquery`, `range`
Red	Inefficient — prioritize for optimization	`all`, `index`

Node display

Each node shows three pieces of information:

Position	Content
Top	Node type, such as `TABLE_SCAN` or `INDEX_SCAN`
Left	Cost — a relative metric evaluated by the optimizer, based on CPU, memory, and disk I/O
Right	Estimated number of returned rows

Start optimization with nodes marked in red. These represent access methods with the highest performance impact.

Glossary

The following terms appear in the visual execution plan.

Term	Plain name	Description
`QUERY_BLOCK`	Query block	A semantic unit of a SQL statement. Each independent query or subquery forms a query block, identified by `select_id` in the EXPLAIN output.
`ATTACHED_SUBQUERIES`	Attached subquery	A subquery attached to a `WHERE`, `HAVING`, or `ON` clause using predicates such as `EXISTS`, `IN`, or `ANY`. Has a logical dependency on the main query.
`CORRELATED_SUBQUERY`	Correlated subquery	A nested query that references columns from an outer query. Requires binding to external context values during execution, which can lead to O(n²) complexity.
`NON_CORRELATED_SUBQUERY`	Non-correlated subquery	A self-contained subquery that can execute independently of the outer query. The optimizer usually pre-calculates and materializes it as a constant.
`MATERIALIZED_FROM_SUBQUERY`	Materialized subquery	An optimization strategy introduced in MySQL 5.6+. Persists the subquery result to an in-memory temporary table; often associated with a derived table in the `subqueryN` format.
`OPTIMIZED_AWAY_SUBQUERIES`	Optimized-away subquery	A subquery completely removed after query rewrite optimization, such as a constant subquery pushdown. Does not appear in the final execution plan.
`QUERY_SPECIFICATIONS`	Query specification structure	The syntactic elements that describe a complete query, including the SELECT list, FROM clause, and other components.
`SELECT_LIST_SUBQUERIES`	SELECT list subquery	A scalar subquery in the projection column (SELECT field list). Must return a single scalar value for each iteration.
`INDEX_SCAN`	Index scan	A data access pattern that uses a B+ tree index. Can be a forward or reverse scan; may involve retrieving data from the table using the index.
`TABLE_SCAN`	Full table scan	Scans all pages of the clustered index when no suitable index is available, or when the proportion of data to access exceeds a threshold. Performance degrades with data volume.
`ORDERING_OPERATION`	Result set sorting	An ORDER BY operation that uses an explicit sorting algorithm such as filesort. May use memory or temporary disk files.
`NESTED_LOOP`	Nested loop join	For each row in the outer table, traverses matching rows in the inner table. Efficient when the join predicate provides effective filtering.
`DUPLICATES_REMOVAL`	Result deduplication	Implements DISTINCT semantics using a unique index on a temporary table or sort-then-filter. Cost depends on data distribution.
`WINDOWING_OPERATION`	Window function calculation	An analytic function such as `ROW_NUMBER` or `RANK` performed on a window defined by an `OVER()` clause. May require sorting the full dataset.
`TABLE`	Base table reference	A physical storage object directly accessed in the execution plan. Contains the table name, alias, and access method (such as `const`, `system`, or `range`).

Optimize SQL from the Instance Sessions page

Important

SQL optimization on the Instance Sessions page is not supported for self-managed MySQL, MongoDB, or RDS for PostgreSQL instances.

Log on to the DAS console.
In the left navigation pane, choose Intelligent O&M Center > Instance Monitoring.
Find the target instance and click the instance ID to open the instance details page.
In the left navigation pane, click Instance Sessions.
In the Instance Sessions area, find the session to optimize and click Optimize.
In the SQL Diagnostic Optimization dialog box, review the diagnosis results. DAS diagnoses the SQL statement based on its complexity, the data volume of the related table, and the database load. Diagnosis may take more than 20 seconds. After diagnosis completes, DAS shows the results, optimization suggestions, and expected performance gains.
To apply the suggestion, click Copy in the upper-right corner and paste the optimized SQL statement into your database client or Data Management (DMS) to run it. To discard the suggestion, click Cancel.

View SQL diagnosis history

Log on to the DAS console.
In the left navigation pane, choose Intelligent O&M Center > Instance Monitoring.
Find the target instance and click the instance ID to open the instance details page.
In the left navigation pane, click Request Diagnostic History.

The history shows all past diagnosis records for the current instance, including the SQL content, diagnosis status, diagnosis time, and diagnosis results.