Re: STRAIGHT_JOIN for OUTER JOINGS
Hi Paul,
STRAiGHT_JOIN is a hint to the optimizer that you want MySQL to process the tables in the order listed in the query. If STRAIGHT_JOIN gives better performance than
INNER JOIN for a two-table query, it means that the MySQL optimizer is choosing a non-optimizal join order by processing the second table first.
In your example query, you have two sub-queries in the FROM clause, so-called derived tables. MySQL will first store the results of the sub-queries in temporary tables and then join these two temporary tables. In many cases, the query will perform better if you can avoid the temporary tables. In your case,
The second sub-query seems unnecessary. The following query should be equivalent:
SELECT a.key, a.col_1, b.col_1 FROM (SELECT * FROM table_a ORDER BY a.key LIMIT 100) a JOIN table_b b ON a.key=b.key;
If you have an index on table_a.key, I would assume that the following equivalent query would perform even better:
SELECT a.key, a.col_1, b.col_1 FROM table_a a JOIN table_b b ON a.key=b.key ORDER BY a.key LIMIT 100;
In the latter query, no temporary tables for derived tables will be needed, and given an index on table_a.key, neither a temporary table for sorting will be needed.
With OUTER JOIN, the order of processing is given. The way MySQL executes LEFT OUTER JOIN, requires that it starts with the left table and join it with the right table. Hence, a STRAIGHT_JOIN directive will have no effect.
Øystein Grøvlen,
Senior Principal Software Engineer,
MySQL Group, Oracle,
Trondheim, Norway