Does MySQL 8.0 support hash joins or batched key access joins? When are they used?
Hash Joins and Batched Key Access (BKA) in MySQL 8.0
MySQL 8.0 introduced significant improvements to its query optimizer, including support for hash joins (starting from MySQL 8.0.18) and the Batched Key Access (BKA) join algorithm. Both are designed to improve join performance in specific scenarios.
- 1
Introduced in MySQL 8.0.18.
- 2
Used automatically by the optimizer when beneficial.
- 3
Particularly efficient for large, non-indexed joins and analytic workloads.
- 4
MySQL builds an in-memory hash table from the smaller table (build side) and probes it with rows from the larger table.
- 1
When JOIN columns are not indexed.
- 2
When the optimizer estimates that nested-loop join would be too slow.
- 3
When joining large datasets in analytical queries (e.g., data warehousing).
- 4
When equality joins are used:
ON a.id = b.id.
You can confirm hash join usage by checking EXPLAIN FORMAT=JSON, which may show: "join_algorithm": "hash_join".
- 1
BKA improves nested-loop joins by batching key lookups instead of performing one lookup per row.
- 2
Reduces random I/O by grouping key lookups using join buffering.
- 3
Supported in MySQL 5.6+ but improved in MySQL 8.0.
BKA is especially effective on disk-based workloads (non-memory tables) where random index lookups are expensive.
- 1
When
optimizer_switch='batched_key_access=on'. - 2
When the join condition can use an index lookup on the inner table.
- 3
When buffering several outer rows reduces I/O cost.
In EXPLAIN, BKA appears as using join buffer (BKA).
- 1
Hash Join → Best for large unindexed joins and analytical workloads.
- 2
Batched Key Access → Best when indexes exist but random I/O is slow; batches key lookups for efficiency.
- 3
Nested-Loop Join → Default; works best when indexes allow very fast lookups on the inner table.
MySQL 8.0 uses both hash joins and Batched Key Access joins to optimize performance. Hash joins accelerate large, equality-based joins when indexes are absent or inefficient. BKA improves nested-loop performance by batching key lookups. MySQL automatically chooses the best algorithm based on table size, indexes, and cost estimation.