STRAIGHT_JOIN is similar to JOIN. The only difference is that STRAIGHT_JOIN does not adjust the order in which tables are joined in the execution plan. STRAIGHT_JOIN can be used to speed up a query if the optimizer joins tables in a sub-optimal order.

Prerequisites

An AnalyticDB for MySQL cluster of version 3.1.3.0 or later is used.
Note For more information about how to view the minor engine version of a cluster, see How can I view the version of an AnalyticDB for MySQL cluster? To update the minor engine version of a cluster, contact technical support.

Syntax

join_table:
    table_reference STRAIGHT_JOIN table_factor [join_condition]

table_reference:
    table_factor
  | join_table

table_factor:
    tbl_name [alias]
  | table_subquery alias
  | ( table_references )

join_condition:
    ON expression 
Note
  • The execution result of STRAIGHT_JOIN is the same as that of INNER JOIN.
  • In the a STRAIGHT_JOIN b statement, Table a that serves as the left table is joined with Table b that serves as the right table. The optimizer does not optimize the join order.

Scenarios

  • You can use STRAIGHT_JOIN if the left and right tables that have specific sizes are specified for INNER JOIN. You can also use STRAIGHT_JOIN to change the join order of tables that are specified for INNER JOIN in an AnalyticDB for MySQL execution plan.
  • To achieve better performance, specify a larger left table and a smaller right table for hash joins, and a smaller left table and a larger right table for nested-loop joins.

Assume that the optimal join order of tables is region, nation, and customer. To improve query efficiency, you can change the first statement to the second one by using STRAIGHT_JOIN and specifying the join order of tables.

SELECT count(*)
       FROM    customer, nation, region
       WHERE c_nationkey = n_nationkey
       AND n_regionkey = r_regionkey
       AND r_name = 'ASIA'; 
SELECT count(*)
       FROM    region STRAIGHT_JOIN nation on n_regionkey = r_regionkey
       STRAIGHT_JOIN customer ON c_nationkey = n_nationkey
       WHERE r_name = 'ASIA';

Examples

In this example, STRAIGHT_JOIN generates the same results as INNER JOIN. For the STRAIGHT_JOIN query, the optimizer does not automatically adjust the join order. The region table is used as the left table. For the INNER JOIN query, the optimizer determines the most efficient order in which to join tables and automatically adjusts the join order.

SELECT count(*)
       FROM region STRAIGHT_JOIN nation on n_regionkey = r_regionkey
       INNER JOIN customer ON c_nationkey = n_nationkey
       WHERE r_name = 'ASIA';