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.

To resolve the preceding issues, AnalyticDB for MySQL allows you to use the /*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.
Note /*reorder_joins*/ is a session-level hint that takes effect only for a specific SQL query statement.

Adjustment method

  • Before adjustment
    • Query statement
      The 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 order
      Tables are joined in the following order based on the preceding SQL statement:
      customer JOIN orders JOIN lineitem JOIN nation;
    • Query results
      The 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.
      1. After the customer and orders tables are joined, 57,069 rows are returned to the tmp1 temporary result set. 1
      2. After the tmp1 temporary result set and the lineitem table are joined, 114,705 rows are returned to the tmp2 temporary result set. 2
      3. After the tmp2 temporary result set and the nation table are joined, 114,705 rows are returned as the final result. 3

      The total number of rows returned from the three joins is: 57,069 + 114,705 + 114,705 = 286,479.

  • After adjustment
    • Query statement
      Add 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 order
      Tables are joined in the following order based on the preceding SQL statement:
      customer JOIN orders JOIN nation JOIN lineitem
    • Query result
      The 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.
      1. After the customer and orders tables are joined, 57,069 rows are returned to the tmp1 temporary result set. 1
      2. After the tmp1 temporary result set and the nation table are joined, 57,069 rows are returned to the tmp2 temporary result set. 2
      3. After the tmp2 temporary result set and the lineitem table are joined, 114,705 rows are returned to as the final result. 3

      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.