According to the document MySQL Explain Output format, MySQL resolves all joins using a nested-loop join method. Is there any way to force MySQL to use a hash join?
Up to the (recently released) 5.6 version, no, you can't. Unfortunately, the only method that the optimizer knows is nested-loop method. As the 5.6 documentation says:
In other words, no hash join algorith or variation has been implemented, not even in version 5.7 - although that is still in development (hope never dies).
There is one alternative. MariaDB, a MySQL fork has implemented various other join methods in the latest (5.3 and 5.5) releases which can be drop-in replacements of MySQL (5.1 and 5.5 respectively) versions.
In their Block-Based Join Algorithms page, they describe the new methods:
There are also some optimizer switches that affect which algorithms will be considered when optimizing a query.
Unfortunately, no. Why ? Two reasons:
REASON #1 : Evaluation Strategy
By design, queries are evaluated in a loop using the following algorithm:
I give more details in my
REASON #2 : Query Optimization with Subqueries
The Query Optimizer has its own secret sauce on top of the Nested Join Approach. This involves removing rows during any transformation phases. This shortens the time for gathering the result set. However, this may not produce correct results when doing
I have posts about this: