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:

MySQL resolves all joins using a nested-loop join method.

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:

In the versions of MariaDB/MySQL before 5.3 only one block-based join algorithm was implemented: the Block Nested Loops (BNL) join algorithm. It could only be used for inner joins. MariaDB 5.3 (and later) enhances the implementation of BNL joins and provides a variety of block-based join algorithms that can be used for inner joins, outer joins, and semi-joins. Block-based join algorithms in MariaDB employ a join buffer to accumulate records of the first join operand before they start looking for matches in the second join operand.

This page documents the various block-based join algorithms.

  • Block Nested Loop (BNL) join
  • Block Nested Loop Hash (BNLH) join
  • Block Index join known as Batch Key Access (BKA) join
  • Block Index Hash join known as Batch Key Access Hash (BKAH) join

There are also some optimizer switches that affect which algorithms will be considered when optimizing a query.

  • Since this answer was created MySQL 5.7 was released. It still treats all joins as nested loops. – Mr. Llama Jan 22 '15 at 21:12
  • I don't believe that's true. I think that wording has just never been updated. It seems like MySQL has had hash joins for 8 years github.com/mysql/mysql-server/blob/… – Evan Carroll Dec 10 '17 at 5:34
  • Actually, I think you're just reading into the MySQL wording, I believe technically a hash join is still a nested loop. – Evan Carroll Dec 10 '17 at 5:41

Unfortunately, no. Why ? Two reasons:

REASON #1 : Evaluation Strategy

By design, queries are evaluated in a loop using the following algorithm:

  • Determine which keys can be used to retrieve the records from tables, and choose the best one for each table.
  • For each table, decide whether a table scan is better that reading on a key. If there are a lot of records that match the key value, the advantages of the key are reduced and the table scan becomes faster.
  • Determine the order in which tables should be joined when more than one table is present in the query.
  • Rewrite the WHERE clauses to eliminate dead code, reducing the unnecessary computations and changing the constraints wherever possible to the open the way for using keys.
  • Eliminate unused tables from the join.
  • Determine whether keys can be used for ORDER BY and GROUP BY.
  • Attempt to simplify subqueries, as well as determine to what extent their results can be cached.
  • Merge views (expand the view reference as a macro)

I give more details in my Mar 11, 2013 post : Is there an execution difference between a JOIN condition and a WHERE condition?

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 DELETE or UPDATE queries using subqueries.

I have posts about this:

UPDATE 2015-10-14 11:09 EDT

Looking over my answer and then looking at @ypercube's answer, I starting to realize that @ypercube actually hinted at changing the optimizer switch setting. Nobody has explicitly mentioned it up to this time, so I will.

Recently, I configured the following for an internal company client in /etc/my.cnf

optimizer_switch = block_nested_loop=off

Then, to skip having to restart mysql, I ran this

mysql> SET GLOBAL optimizer_switch = block_nested_loop=off;

The client's troublesome join queries were now running better.

Please read the MySQL Documentation on optimizer_switch.

As for the actually question, only the MySQL Query Optimizer can decide the best join method. Yet, you can help by disabling block_nested_loop.

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.