Does MySQL 8.0 support hash joins or batched key access joins? When are they used?

When JOIN columns are not indexed.

When the optimizer estimates that nested-loop join would be too slow.

When joining large datasets in analytical queries (e.g., data warehousing).

When equality joins are used: ON a.id = b.id.

BKA improves nested-loop joins by batching key lookups instead of performing one lookup per row.

Reduces random I/O by grouping key lookups using join buffering.

Supported in MySQL 5.6+ but improved in MySQL 8.0.

When optimizer_switch='batched_key_access=on'.

When the join condition can use an index lookup on the inner table.

When buffering several outer rows reduces I/O cost.

Hash Join → Best for large unindexed joins and analytical workloads.

Batched Key Access → Best when indexes exist but random I/O is slow; batches key lookups for efficiency.

Nested-Loop Join → Default; works best when indexes allow very fast lookups on the inner table.