The best query tuning win I ever shipped involved no tuning at all. We moved a reporting replica from MySQL 5.7 to 8.0.20, and a nightly reconciliation join that had taken 38 minutes for a solid year finished in four. Nobody touched the query, the indexes, or the config. The optimizer had simply stopped running block nested loop and started hashing. That same upgrade quietly made two of my carefully maintained indexes irrelevant, and on one query an index I added out of habit made things measurably worse. Hash joins changed what good join tuning means on MySQL, and a fair amount of 5.7-era advice is now actively harmful.
This is the model I work from now: when the 8.0 optimizer chooses a hash join, what join_buffer_size actually budgets, how to read the plan in EXPLAIN FORMAT=TREE, when an index beats the hash and when it loses, and how to switch the feature off for one session without lying to yourself about production.
When does the MySQL optimizer choose a hash join?
A hash join is chosen when there is no usable index on the inner table's join columns — broadly, the cases where 5.7 fell back to block nested loop. The feature landed in 8.0.18 for plain no-index equi-joins, and 8.0.20 extended it to every shape BNL used to cover — semijoins and antijoins, non-equi conditions, even Cartesian products — then deleted block nested loop from the executor entirely. So when you see a hash join in a plan, read it as a message: the optimizer found no access path worth driving the inner side row by row, and decided that hashing the smaller input and probing it with one pass over the larger input was cheaper. It is not a new strategy for indexed joins. It is a strictly better fallback for unindexed ones.
The build side is normally the smaller input after filters, and which side builds matters for memory. The optimizer decides from its row estimates, and when those estimates are stale the hash join still runs — it just may build on the wrong side and spill. That is one reason I check index statistics and index dives before touching anything else on a plan I do not like.
How does join_buffer_size budget the hash table?
join_buffer_size is the memory ceiling for the in-memory hash table. The executor hashes build-side rows into a table that may grow up to join_buffer_size per join instance, and the default is 256KB, which is tiny for anything beyond toy joins. When the build input exceeds the budget the join does not fail — it spills. Both inputs get partitioned by hash value into chunks written to temporary files, and the join processes one chunk pair at a time, which means those rows are written once and read back. Spilling converts a memory shortage into an I/O tax, and on a large build side you pay it on both inputs.
I watched a customer analytics join drop from 11 minutes to 50 seconds after raising join_buffer_size for that one session from the default to 32MB, because the build side fit in memory and the spill vanished. The knob is per join instance, not per query: a statement with two hash joins can allocate two buffers, and the total scales with concurrent connections, so raising it globally to some heroic value is how you OOM a box at rush hour. Set it at session level for the heavy report, keep the global default sane, and let the instrumentation in MySQL memory usage diagnosis confirm what the join really allocated.
How do you read a hash join in EXPLAIN FORMAT=TREE?
Use FORMAT=TREE or EXPLAIN ANALYZE, because the traditional tabular EXPLAIN hides the join algorithm — it shows ALL and ALL and leaves you guessing. The tree makes the structure explicit: the probe side sits on top, and the build side hangs under a Hash node.
EXPLAIN FORMAT=TREE
SELECT c.country, COUNT(*)
FROM orders o
JOIN customers c ON c.customer_id = o.customer_id
WHERE c.country = 'DE'
GROUP BY c.country;
-- plan shape:
-- -> Inner hash join (o.customer_id = c.customer_id)
-- -> Table scan on o
-- -> Hash
-- -> Filter: (c.country = 'DE')
-- -> Table scan on cRead it bottom up: customers is scanned and filtered, the survivors are hashed, then orders is scanned once and each row probes the hash. Run the same statement through EXPLAIN ANALYZE and the identical tree carries actual row counts and milliseconds per iterator, which is how you catch a build side estimated at 5,000 rows that materialized at 2 million. I walk that workflow in the EXPLAIN ANALYZE reading guide and no longer tune a join without it.
Can an index make a join slower than a hash join?
Yes, and this is the 8.0 lesson that bites 5.7 veterans. Converting a hash join into index nested loop means one B-tree descent and page fetch on the inner table per outer row. When the join touches a large fraction of the inner table, that is millions of random reads against the hash join's two sequential scans. I measured exactly this on a 2-million-row fact table joined to a 3-million-row dimension: a fresh index flipped the plan to nested loop and the query took 19 minutes of random I/O, where the hash join had finished in 41 seconds of sequential reads. The index did not accelerate the join. It just made the plan look more respectable.
The same index is gold when the join is selective. If the outer side produces 300 rows after filters, then 300 indexed lookups beat scanning 3 million rows every single time, and it is not close. My rule: hash joins are for bulk, nested loop is for selectivity. Before adding an index to kill a hash join, measure both plans with EXPLAIN ANALYZE and keep whichever the data prefers, not whichever your habits prefer.
What happened to block nested loop, and does BKA still matter?
Block nested loop is gone: removed in 8.0.20, with the hash join taking over every plan shape that used to fall back to it. If a runbook still talks about BNL buffers, the concept survives only inside the name join_buffer_size, which now budgets hash tables. Batched key access is a different animal and still exists: for indexed nested loop joins, BKA batches inner-table keys, sorts them through multi-range read, and fetches rows in something closer to storage order. It stays off by default via the batched_key_access flag in optimizer_switch, and in my experience it pays mainly on cold, spinning, or network storage where read order dominates. On cached NVMe I have never seen it win a benchmark I trusted.
Should you disable hash joins with optimizer_switch?
For diagnosis, yes; as a production strategy, no. You can flip the feature off for one session, or hint a single query, to see what the optimizer would have picked without it. One naming fossil to know first: the hash_join switch and NO_HASH_JOIN hint were already dead by 8.0.19, and the working controls still answer to the name of the thing hash join replaced:
SET SESSION optimizer_switch = 'block_nested_loop=off';
SELECT /*+ NO_BNL(c) */ c.country, COUNT(*)
FROM orders o
JOIN customers c ON c.customer_id = o.customer_id
WHERE c.country = 'DE'
GROUP BY c.country;If the query gets faster with hash joins off, the correct conclusion is not that hash joins are bad here; it is that this join is missing an index or running on stale statistics. A forced plan is a bug report against your metadata, not a configuration. Session-level switches also have a way of leaking into connection pools and becoming invisible production behavior, which is exactly the failure mode catalogued in optimizer_switch pitfalls. Fix the access path, then let the optimizer choose.
Where MonPG stands on MySQL
I build MonPG, so the honest disclaimer first: MonPG monitors PostgreSQL today, and MySQL support is in active development, not shipped. The signals this article leans on — hash joins appearing and disappearing in plans after upgrades, spill-driven slowdowns, per-query cost swings — are exactly what the MySQL work is meant to surface from slow log and performance_schema digests, so a plan flip shows up as a latency step change instead of a mystery. The MySQL monitoring (coming soon) page tracks that work as it lands. Until it ships, the same evidence-first approach runs today on the PostgreSQL side, and the rest of these MySQL field notes live on the blog.