STRAIGHT_JOIN over INNER JOIN

{question}

When do we use "straight_join" query hint in a query?

{question}

{answer}

At times, the optimizer won't be able to decide on the best joining order of the tables in a multi-table join query and puts the tables in the wrong order. When we are sure of the join order, which performs better than the one chosen by the optimizer, we use hints to force the execution plan.

In SingleStore, the hint STRAIGHT_JOIN forces tables to be joined in the order listed in the FROM clause. STRAIGHT_JOIN is similar to INNER JOIN, except that the left table is always read before the right table.

The hint can be used in either manner, as shown below.

​​​​​​1. table_1 straight_join table_2
2. select straight_join...

Method 1 : table_1 straight_join table_2

Create three test tables:

create table table_1 (col1 int primary key, col2 varchar(5));
create table table_2 (col1 int primary key, col2 varchar(5));
create table table_3 (col1 int primary key, col2 varchar(5));

Join the tables in INNER JOIN manner and check the explain plan:

explain select table_1.col1, table_2.col2, table_3.col2 
from table_1 
join table_2 on table_1.col1=table_2.col1 
join table_3 on table_1.col1 = table_3.col1;

explain select table_1.col1, table_2.col2, table_3.col2
from table_1
straight_join table_2 on table_1.col1=table_2.col1
straight_join table_3 on table_1.col1 = table_3.col1;

explain select straight_join table_1.col1, table_2.col2, table_3.col2
from table_1
join table_2 on table_1.col1=table_2.col1
join table_3 on table_1.col1 = table_3.col1;

{answer}