Query performance problems in AnalyticDB for MySQL often stem from two root causes: skewed data distribution and complex query structures. Checking execution plans is the most reliable way to diagnose bottlenecks. This topic explains the common issues that execution plans reveal and how to address each one.
Common issues that affect query performance
Hash join: inner and outer table selection
In a hash join, AnalyticDB for MySQL uses the right table to build a hash table and the left table to probe it. For this to work efficiently, the right table must be smaller than the left table — a larger right table increases hash table size and adds overhead.
To check whether AnalyticDB for MySQL has selected tables correctly, check the number of data entries after the tables are joined and filtered to determine whether the left and right tables are selected in a proper way. Note that intermediate join results and join type also affect the decision, so the row count alone is not always conclusive.
Join order
Optimizing join order is an NP-hard problem. AnalyticDB for MySQL uses two join reordering policies:
| Policy | When it applies |
|---|---|
| Left Deep Tree (LDT) | Simple queries |
| Bushy Tree (BT) | Complex queries — joins tables with the most selective filters first |
For data-intensive queries that join many tables, prioritize tables with the most selective filter conditions early in the join order. Filtering out rows early reduces the data volume that subsequent joins must process.
Distributed aggregation
AnalyticDB for MySQL runs aggregation in two stages:
-
Partial aggregation — each compute node aggregates its local data to reduce data volume.
-
Final aggregation — results are reshuffled by the aggregation columns and combined across nodes.
When data is skewed or the aggregation columns are nearly unique, partial aggregation cannot reduce data volume and instead adds overhead. If the data distribution is skewed, the query optimizer makes inaccurate estimates, which affects the aggregation performance.
Other issues
The issues above are the most common ones visible in execution plans. More complex problems — such as full table scans on a large table with no filter condition or predicates not pushed down to the storage engine — require investigation by the AnalyticDB for MySQL expert service team.
Improve execution plans
Collect table statistics
When to apply: The query optimizer is generating a suboptimal plan for a recently created table, or statistics have not been collected yet.
The query optimizer estimates execution plan cost using table statistics, which are collected automatically. To check whether statistics exist for a table, run:
SELECT * FROM information_schema.column_statistics;information_schema.column_statistics is available only on clusters running V3.1.6 and later.If the table has no statistics, manually trigger collection before automatic collection runs. For details, see Automatic statistics collection and Manually collect statistics.
Change join order
When to apply: The default join order is suboptimal due to uneven data distribution or query complexity, and you want to test a specific join sequence.
Use the reorder_joins hint to control how AnalyticDB for MySQL orders table joins:
| Hint | Effect |
|---|---|
/* + reorder_joins=false */ |
Disables automatic reordering. Change the table order in the FROM clause to specify the join sequence. |
/* + reorder_joins=true */ |
Re-enables automatic reordering. The query optimizer generates the join order. |
Example: For tables nation, region, and customer, the default join order is region > nation > customer. To test the order customer > nation > region, add the hint and reorder the FROM clause:
/*+ reorder_joins=false */
EXPLAIN SELECT count(*)
FROM customer, nation, region
WHERE c_nationkey = n_nationkey
AND n_regionkey = r_regionkey
AND r_name = 'ASIA';The resulting execution plan:
| Plan Summary |
+---------------+
1- Output[ Query plan ]
2 -> Aggregate (FINAL)
3 -> LocalExchange[SINGLE]
4 -> Exchange[GATHER]
5 -> Aggregate (PARTIAL)
6 -> InnerJoin[Hash Join]
7 - Project
8 -> InnerJoin[Hash Join]
9 - ScanProject {table: customer}
10 -> TableScan {table: customer}
11 -> LocalExchange[HASH]
12 -> Exchange[REPLICATE]
13 - ScanProject {table: nation}
14 -> TableScan {table: nation}
15 -> LocalExchange[HASH]
16 -> Exchange[REPLICATE]
17 - ScanProject {table: region}
18 -> TableScan {table: region}reorder_joins parameter to change the table order, AnalyticDB for MySQL also provides a plan hint for the join order. You can embed the leading_join_order hint at the beginning of the query to adjust the AnalyticDB for MySQL join order. For the preceding example, you can obtain the same join order by adding a plan hint for the join order before the query.
/*+ leading_join_order=((c n) r) */
EXPLAIN SELECT count(*)
FROM nation n , region r, customer c
WHERE c_nationkey = n_nationkey
AND n_regionkey = r_regionkey
AND r_name = 'ASIA'; Use the leading_join_order hint
When to apply: You need to specify a precise join order using nested table groupings, or a table appears multiple times in the query.
The leading_join_order hint gives you fine-grained control over join order using a nested syntax. Add it to the beginning of the query.
If a table is referenced multiple times in a query, assign it an alias before using this hint.
The following query produces the same join order (customer > nation > region) as the reorder_joins example above:
/*+ leading_join_order=((c n) r) */
EXPLAIN SELECT count(*)
FROM nation n , region r, customer c
WHERE c_nationkey = n_nationkey
AND n_regionkey = r_regionkey
AND r_name = 'ASIA';