MySQL 8.0 introduced several major improvements to its JOIN optimizer, making join execution faster, more efficient, and more scalable than in MySQL 5.x. These improvements enhance join algorithms, join buffering, cost estimation, and the ability to handle large datasets.

1. Introduction of Hash Joins (MySQL 8.0.18+)

1
MySQL 5.x relied almost entirely on nested-loop joins.
2
MySQL 8.0 added a cost-based Hash Join algorithm for equality joins.
3
MySQL builds an in-memory hash table from the smaller table and probes it using the larger table.
4
Particularly beneficial when JOIN columns are not indexed or when joining large analytical datasets.

When Hash Joins Are Used

1
For equality joins (ON t1.col = t2.col).
2
When the optimizer estimates that nested-loop joins are slower.
3
When indexes are missing or not selective.
4
For large datasets typical in reporting and analytics workloads.

You can see hash join usage in EXPLAIN FORMAT=JSON under "join_algorithm": "hash_join".

2. Improvements to Batched Key Access (BKA) Join

1
BKA existed since MySQL 5.6, but MySQL 8.0 improved buffering and batching efficiency.
2
BKA reduces random I/O by batching index lookups instead of performing single-row lookups.
3
Enhanced performance for joins involving secondary indexes on large tables.

BKA appears in EXPLAIN as using join buffer (BKA) when enabled.

Enable BKA

3. Improved Join Buffering and Block Nested-Loop Joins

1
MySQL 8.0 improved how join buffers are allocated and reused.
2
Join buffers now dynamically resize based on workload.
3
Block nested-loop joins perform fewer disk reads for non-indexed joins.

4. Better Cost-Based Join Order Optimization

1
MySQL 8.0 has a new cost model for selecting join order.
2
Optimizer considers more join permutations than earlier versions.
3
Better cardinality estimates using histogram statistics.
4
Improves performance for JOINs involving many tables.

5. Histogram-Based Join Selectivity Estimates

1
Introduced in MySQL 8.0 for more accurate stats on non-indexed columns.
2
Reduces incorrect join order choices caused by poor cardinality estimates.
3
Results in fewer slow query plans and more efficient join execution.

6. Improvements to Derived Tables and Subquery-to-JOIN Optimization

1
MySQL 8.0 materializes fewer derived tables during join execution.
2
More subqueries are merged into outer queries and optimized as joins.
3
Reduces temporary table usage and improves join performance.

7. Enhanced Multi-Threaded and Parallel Query Processing (InnoDB Scans)

1
While not full parallel JOIN execution, InnoDB now performs faster table and index scans.
2
Improves the performance of joins involving large sequential reads.

MySQL 8.0 significantly improved join optimization by adding hash joins, enhancing Batched Key Access, improving join buffer algorithms, introducing histogram-based cardinality estimation, and choosing better join orders. These changes make JOIN operations much faster, especially for analytical workloads and large datasets.

Difficulty: 6/10

Topics: join execution plans, hash joins, batched key access

Scenario Questions

0-2 years experience

1
You're running a query joining two medium-sized tables and it's taking 10 seconds. You upgrade from MySQL 5.7 to 8.0 and it drops to 1 second. What’s the most likely reason?
2
Your junior teammate says they can't get a join to use an index in MySQL 8.0 — what’s one thing they should check that wasn’t relevant in 5.7?
3
If you run EXPLAIN on a join in MySQL 8.0 and see 'Hash Join' in the type column, what does that tell you about the underlying optimization?

2-5 years experience

1
Your analytics dashboard started timing out after upgrading to MySQL 8.0 — the query plan changed and now uses a hash join, but memory usage spiked. What would you investigate first?
2
A critical report that used to run in 30 seconds on MySQL 5.7 now takes 2 minutes on 8.0 after a schema change. The join is now on a non-indexed column. Why might this be happening?
3
You’re debugging a slow join query in MySQL 8.0 and notice the optimizer chose a nested loop instead of a hash join — what factors could have caused that decision?

5-8 years experience

1
You're designing a data warehouse pipeline that joins fact and dimension tables with 10M+ rows. How would you decide whether to rely on MySQL 8.0’s hash join or refactor to use materialized views?
2
Your team is migrating from MySQL 5.7 to 8.0 and some queries are now slower. The optimizer chose a different join order — how would you diagnose and stabilize performance without downgrading?
3
A join between two large tables is causing temporary table spills to disk in MySQL 8.0. How would you tune the system to avoid this, and what tradeoffs are you considering between memory, I/O, and query latency?

8+ years experience

1
You’re leading a migration from MySQL 5.7 to 8.0 across 50+ services. Some teams report unpredictable performance regressions on joins — how do you design a rollout strategy that minimizes risk and ensures consistent behavior?
2
Your company is considering switching from MySQL to PostgreSQL for analytics workloads. How would you evaluate whether MySQL 8.0’s join optimizations are sufficient to justify staying, given the cost of migration and long-term maintenance?
3
A legacy reporting system still runs on MySQL 5.7 and depends on nested loop join behavior. You need to modernize it without breaking downstream consumers. How do you approach this without forcing a full rewrite?

Follow-up Questions

❓How would you verify that MySQL 8.0 is using a hash join instead of a nested loop join?
❓What happens if you disable hash joins and run the same query on an older MySQL version?
❓Can you think of a scenario where hash joins might perform worse than nested loops?