This topic describes recursive query that is supported by AnalyticDB for MySQL clusters of version 3.1.3 and later. Similar to the JOIN statement, the STRAIGHT_JOIN statement does not adjust the sequence of the left and right tables in the execution plan. STRAIGHT_JOIN can be used to join optimizers when optimizers process tables in descending order.
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
- The execution result of STRAIGHT_JOIN is the same as the execution result of INNER JOIN.
- When the a STRAIGHT_JOIN b syntax is executed, Table A is the left table, and Table B is the right table to join each other. Table A and table B are directly joined. The optimizer does not optimize the join order.
This syntax specifies the left and right tables when INNER JOIN is executed. This syntax can be used to specify the table size or to check whether the left and right tables selected by INNER JOIN in the execution plan of AnalyticDB for MySQL are found unreasonable.
By default, you can choose a large table to the left and a small table to the right in a hash join scenario. This way, AnalyticDB for MySQL can achieve better performance. If nested loop join is specified, select a small table to the left and a large table to the right.
If the best join order is region, nation, or customer in the following SQL statement, you can make queries more efficient, modify the first SQL statement to the second SQL statement and specify the join order.
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';
In this example, STRAIGHT_JOIN generates the same results as the results of INNER JOIN. Then, the optimizer determines whether the result table generated by STRAIGHT_JOIN is the same as the result table of the left and right tables when the customer table is joined.
SELECT count(*) FROM region STRAIGHT_JOIN nation on n_regionkey = r_regionkey INNER JOIN customer ON c_nationkey = n_nationkey WHERE r_name = 'ASIA';