Using join buffer (hash join) in MySQL EXPLAIN

What it does

Using join buffer (hash join) on a table’s row in EXPLAIN means MySQL joins that table using a hash join. It reads the rows of the earlier table (the build input), puts their join keys in a hash table in memory, then scans this table (the probe input) and looks each row’s key up in the hash table.

EXPLAIN ANALYZE shows the structure more clearly. The input under -> Hash is the one held in memory; the other is scanned:

-> Inner hash join (i.sku = p.sku)
    -> Table scan on i
    -> Hash
        -> Table scan on p

Since MySQL 8.0.20 the hash join has replaced the older block nested loop, so you won’t see Using join buffer (Block Nested Loop) on 8.4.

When the planner picks it

When two tables are joined and no index can be used to look up matching rows in the second one: the join columns aren’t indexed, are wrapped in a function, or are of different types. Without an index, the alternative would be to scan the second table once for every row of the first; the hash join scans each table once.

If an index exists on the join column of one of the tables, MySQL usually prefers a nested loop that looks rows up through it (ref or eq_ref in the type column), and no join buffer appears.

Reading its numbers

• The row with Using join buffer (hash join) is usually type ALL: the probe table is scanned in full. Its rows is that table’s size; filtered is often a guess.
• The cost and rows estimate on Inner hash join can be huge (18e+6 below) because MySQL assumes little about how many keys match. Look at the actual rows instead.
• In EXPLAIN ANALYZE, compare the rows read by the probe-side Table scan with the rows= the join returns. Reading 200,000 rows to return 223 is the sign that an index would help.

The hash table must fit in join_buffer_size (256 KB by default). If the build input is larger, MySQL writes chunks to temporary files on disk and joins them piece by piece. That still works, but it’s slower, and a join that needs more files than open_files_limit allows can fail.

When it’s a problem

For reports that join large parts of two tables, a hash join is often the best plan. It becomes a problem when:

• The query is selective and runs often: each run scans the whole probe table to return a few rows. An index on the probe table’s join column turns it into a nested loop that reads only the matching rows.
• The build side is big, so the hash table spills past join_buffer_size to disk.
• The join columns don’t match in type or collation, so an index you have can’t be used. Fix the column definitions rather than the query.

The usual fix is an index on the join column of the larger table: here, order_items (sku).

Example

On MySQL 8.4.11, order lines and a small product table, neither indexed on sku:

CREATE TABLE order_items (
  id int NOT NULL AUTO_INCREMENT PRIMARY KEY,
  order_id int NOT NULL,
  sku varchar(20) NOT NULL,
  quantity int NOT NULL,
  price decimal(10,2) NOT NULL
);                                   -- 200,000 rows

CREATE TABLE products (
  id int NOT NULL AUTO_INCREMENT PRIMARY KEY,
  sku varchar(20) NOT NULL,
  name varchar(60) NOT NULL,
  category varchar(20) NOT NULL
);                                   -- 900 rows

Units sold per category, a report over everything:

EXPLAIN SELECT p.category, SUM(i.quantity) AS units
FROM order_items i JOIN products p ON p.sku = i.sku
GROUP BY p.category;

+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+--------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra                                      |
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+--------------------------------------------+
|  1 | SIMPLE      | p     | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    900 |   100.00 | Using temporary                            |
|  1 | SIMPLE      | i     | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 199535 |    10.00 | Using where; Using join buffer (hash join) |
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+--------------------------------------------+

-> Table scan on <temporary>  (actual time=103..103 rows=4 loops=1)
    -> Aggregate using temporary table  (actual time=103..103 rows=4 loops=1)
        -> Inner hash join (i.sku = p.sku)  (cost=18e+6 rows=18e+6) (actual time=0.556..53.3 rows=200000 loops=1)
            -> Table scan on i  (cost=2.48 rows=199535) (actual time=0.203..27.3 rows=200000 loops=1)
            -> Hash
                -> Table scan on p  (cost=91.2 rows=900) (actual time=0.0635..0.164 rows=900 loops=1)

The 900 products go into the hash table and the 200,000 order lines are scanned once against it: the join takes 53 ms, and every order line is needed anyway. A fine plan.

Now the lines for one product:

EXPLAIN ANALYZE SELECT i.order_id, i.quantity, i.price
FROM order_items i JOIN products p ON p.sku = i.sku
WHERE p.name = 'Product 42';

-> Inner hash join (i.sku = p.sku)  (cost=1.8e+6 rows=1.8e+6) (actual time=0.379..37.2 rows=223 loops=1)
    -> Table scan on i  (cost=24 rows=199535) (actual time=0.139..22.9 rows=200000 loops=1)
    -> Hash
        -> Filter: (p.`name` = 'Product 42')  (cost=91.2 rows=90) (actual time=0.0617..0.212 rows=1 loops=1)
            -> Table scan on p  (cost=91.2 rows=900) (actual time=0.0536..0.141 rows=900 loops=1)

One product in the hash table, yet all 200,000 order lines are scanned to find 223. An index on the probe side’s join column:

CREATE INDEX idx_items_sku ON order_items (sku);

+----+-------------+-------+------------+------+---------------+---------------+---------+------------------------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key           | key_len | ref                    | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+---------------+---------+------------------------+------+----------+-------------+
|  1 | SIMPLE      | p     | NULL       | ALL  | NULL          | NULL          | NULL    | NULL                   |  900 |    10.00 | Using where |
|  1 | SIMPLE      | i     | NULL       | ref  | idx_items_sku | idx_items_sku | 82      | seo_mysqlexplain.p.sku |  220 |   100.00 | NULL        |
+----+-------------+-------+------------+------+---------------+---------------+---------+------------------------+------+----------+-------------+

-> Nested loop inner join  (cost=7052 rows=19887) (actual time=0.172..0.831 rows=223 loops=1)
    -> Filter: (p.`name` = 'Product 42')  (cost=91.2 rows=90) (actual time=0.0512..0.228 rows=1 loops=1)
        -> Table scan on p  (cost=91.2 rows=900) (actual time=0.043..0.152 rows=900 loops=1)
    -> Index lookup on i using idx_items_sku (sku=p.sku)  (cost=55.5 rows=221) (actual time=0.118..0.589 rows=223 loops=1)

The join buffer is gone: MySQL finds the product, then looks up its 223 lines through the index. 37 ms became 0.8 ms. (The small products table is still scanned; an index on products (name) would remove that too.)

In Inlet

Run EXPLAIN or EXPLAIN ANALYZE in Inlet’s query editor like any other statement (⌘↩). With your own Anthropic API key, Ask Claude (⌘L) can explain a plan; it sends the schema and the plan, never rows. The structure editor shows the CREATE INDEX statement before it runs.