AnalyticDB for MySQL automatically optimizes join orders for complex queries, but this automatic adjustment may select a suboptimal order when data characteristics are complex or skewed. In those cases, you can disable automatic adjustment and specify the join order directly in your SQL statement.
How it works
When you run a multi-table JOIN query, AnalyticDB for MySQL evaluates possible join sequences and selects one it estimates will minimize intermediate data. This automatic join order adjustment is enabled by default.
When filter conditions or table data distributions change, the optimizer may not accurately estimate query characteristics across all scenarios. A suboptimal join order causes intermediate result sets to expand unnecessarily and increases memory usage.
Use the /*+ reorder_joins*/ hint to control this behavior:
| Hint | Effect |
|---|---|
/*+ reorder_joins=true*/ | Enables automatic join order adjustment (default) |
/*+ reorder_joins=false*/ | Disables automatic adjustment; the optimizer follows the join order written in your SQL |
/*+ reorder_joins*/ is a session-level hint. It applies only to the SQL statement it is added to.
Adjust the join order
This example uses TPC-H Query 10 to show how reordering tables in the FROM clause reduces intermediate result set sizes. For more information about the TPC-H benchmark, see TPC-H Version 2 and Version 3.
Before adjustment
The original query joins customer, orders, lineitem, and nation in that order. The /*+ reorder_joins=false*/ hint disables automatic adjustment so the query executes in the written order — simulating a scenario where the default automatic adjustment produces a suboptimal join sequence:
SELECT c_custkey,
c_name,
Sum(l_extendedprice * (1 - l_discount)) AS revenue,
c_acctbal,
n_name,
c_address,
c_phone,
c_comment
FROM customer c,
orders o,
lineitem l,
nation n
WHERE c_custkey = o_custkey
AND l_orderkey = o_orderkey
AND o_orderdate >= date '1993-10-01'
AND o_orderdate < date '1993-10-01' + INTERVAL '3' month
AND l_returnflag = 'R'
AND c_nationkey = n_nationkey
GROUP BY c_custkey,
c_name,
c_acctbal,
c_phone,
n_name,
c_address,
c_comment
ORDER BY revenue DESC
LIMIT 20;Join order: customer JOIN orders JOIN lineitem JOIN nation
The execution plan produces these intermediate result sets:
customerJOINorders→ 57,069 rows (tmp1)
tmp1 JOIN
lineitem→ 114,705 rows (tmp2)
tmp2 JOIN
nation→ 114,705 rows (final result)
Total rows across all joins: 57,069 + 114,705 + 114,705 = 286,479
For instructions on viewing an execution plan, see Use execution plans to analyze queries.
After adjustment
Reorder the tables in the FROM clause to join nation before lineitem. The /*+ reorder_joins=false*/ hint ensures the optimizer follows the reordered sequence:
/*reorder_joins=false*/
SELECT c_custkey,
c_name,
Sum(l_extendedprice * (1 - l_discount)) AS revenue,
c_acctbal,
n_name,
c_address,
c_phone,
c_comment
FROM customer c,
orders o,
nation n,
lineitem l
WHERE c_custkey = o_custkey
AND c_nationkey = n_nationkey
AND l_orderkey = o_orderkey
AND o_orderdate >= date '1993-10-01'
AND o_orderdate < date '1993-10-01' + INTERVAL '3' month
AND l_returnflag = 'R'
GROUP BY c_custkey,
c_name,
c_acctbal,
c_phone,
n_name,
c_address,
c_comment
ORDER BY revenue DESC
LIMIT 20;Join order: customer JOIN orders JOIN nation JOIN lineitem
The execution plan produces these intermediate result sets:
customerJOINorders→ 57,069 rows (tmp1)
tmp1 JOIN
nation→ 57,069 rows (tmp2)
tmp2 JOIN
lineitem→ 114,705 rows (final result)
Total rows across all joins: 57,069 + 57,069 + 114,705 = 228,843
The adjusted join order reduces total intermediate rows by 20%, lowering memory usage and improving query performance.