This topic describes how to manually adjust join orders by using hints.
Overview
AnalyticDB for MySQL supports queries that contain complex joins and provides the automatic join order adjustment feature. However, filter conditions of query statements and tables may change at any time. If the data characteristics are complex, the automatic join order adjustment feature may be unable to estimate the query characteristics in all scenarios and select an optimal join order. Inappropriate join orders may cause issues such as data expansion in intermediate result sets and high memory usage, which further affects the query performance.
/*reorder_joins*/
hint to specify whether to enable the automatic join order adjustment feature.
/*reorder_joins=true*/
: The automatic join order adjustment feature is enabled. After this feature is enabled, join orders are automatically adjusted by the system. By default, this feature is enabled for AnalyticDB for MySQL. When you execute SQL queries, join orders are automatically adjusted without the need to use this hint./*reorder_joins=false*/
: The automatic join order adjustment feature is disabled. After this feature is disabled, you can manually adjust join orders based on the data characteristics of queries. This allows the queries to be executed based on the join orders in the written SQL statements.
/*reorder_joins*/
is a session-level hint that takes effect only for a specific SQL query statement.
Adjustment method
- Before adjustment
- Query statementThe following example shows an original Query10 statement.Note
- The following example demonstrates how to manually adjust the join order and the query effects. In this example, Query10 in the TPC-H test is used. For more information about the TPC-H test, visit TPC-H.
- By default, the automatic join order adjustment feature is enabled for AnalyticDB for MySQL. When the following query statement is executed, the
/*reorder_joins=false*/
hint is used to simulate the scenario where the join order is inappropriate.
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 orderTables are joined in the following order based on the preceding SQL statement:
customer JOIN orders JOIN lineitem JOIN nation;
- Query resultsThe following temporary results of each join in the execution plan are returned.Note For more information about how to view an execution plan, see Use execution plans to analyze queries.
- After the
customer
andorders
tables are joined, 57,069 rows are returned to thetmp1
temporary result set. - After the
tmp1
temporary result set and thelineitem
table are joined, 114,705 rows are returned to thetmp2
temporary result set. - After the
tmp2
temporary result set and thenation
table are joined, 114,705 rows are returned as the final result.
The total number of rows returned from the three joins is: 57,069 + 114,705 + 114,705 = 286,479.
- After the
- Query statement
- After adjustment
- Query statementAdd the
/*reorder_joins=false*/
hint to the SQL statement to disable the automatic join order adjustment feature of AnalyticDB for MySQL, and manually adjust the join order. The following example shows the adjusted SQL statement:/*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 orderTables are joined in the following order based on the preceding SQL statement:
customer JOIN orders JOIN nation JOIN lineitem
- Query resultThe following temporary results of each join in the execution plan are returned.Note For more information about how to view an execution plan, see Use execution plans to analyze queries.
- After the
customer
andorders
tables are joined, 57,069 rows are returned to thetmp1
temporary result set. - After the
tmp1
temporary result set and thenation
table are joined, 57,069 rows are returned to thetmp2
temporary result set. - After the
tmp2
temporary result set and thelineitem
table are joined, 114,705 rows are returned to as the final result.
The total number of rows returned from the three joins is: 57,069 + 57,069 + 114,705 = 228,843.
The total number of rows returned after adjustment is reduced by 20% compared with that before adjustment. The preceding comparison shows that different join orders affect the sizes of intermediate temporary result sets. If join orders in AnalyticDB for MySQL are inappropriate, you can manually adjust the join orders to improve SQL query performance.
- After the
- Query statement