Parameters (Batched Key Access join)
About this exampleAbout this example
Example query this animation executes
-- BKA: batch outer keys → one Multi-Range Read on the inner index SELECT o.order_id, d.name FROM orders o JOIN departments d ON d.dept_no = o.dept_no; -- index on departments(dept_no); departments.id is the primary key
BKA fills the join buffer with orders rows and sends all their dept_no keys to InnoDB as one Multi-Range Read. InnoDB finds each key in the dept_no index, sorts the primary keys it found, and reads the departments rows in primary key order instead of jumping around.
What you'll see in the animation
- Purple zone (top): orders rows fill the join buffer one by one ('#1001 dept_no=30'), forming one batch.
- Blue zone (middle): the batch's dept_no keys go to InnoDB as one Multi-Range Read (MRR). The index on departments(dept_no) gives each key's primary key ('dept_no=30 → id=2'), and the primary keys (InnoDB's row ids) are then sorted.
- Green zone (bottom): InnoDB reads the departments rows in primary key order, so the reads move forward through the table instead of jumping around; each row is joined with its order from the buffer.
- A sweep bar moves across the green zone to show the reads moving forward in key order.
- The readout below compares random I/Os (without BKA) vs sequential I/Os (with BKA) and shows the speedup factor.
- Next step fills the buffer, looks up the keys, sorts them, then reads one row at a time; the simplified code beside the drawing highlights the lines each step runs.
BKA cost model
Rows per batch ?How many outer rows fit in one join_buffer_size chunk. More rows per batch = fewer batches = fewer MRR round-trips.
—
Number of batches ?ceil(outer_rows / rows_per_batch). Each batch triggers one MRR request to the storage engine.
—
B+tree height ?Levels in the inner index B+tree. Each batch traverses this many levels before reaching leaf pages.
—
Random I/Os (without BKA) ?Without BKA, each outer row does an independent random index lookup: outer_rows x height page reads.
—
Sequential I/Os (with BKA) ?With BKA + MRR, the row ids found in the index are sorted, so the engine reads the rows in primary key order: batches x (height + rows_per_batch).
—
Speedup factor ?random_ios / sequential_ios. Higher = more benefit from BKA. Biggest gains on HDD; still helps on SSD.
—
I/O operations vs outer rows (log–log)
Learn more — BKA vs simple Nested Loop, and when to enable it
Simple Nested Loop does one random index lookup per outer row. If the inner table is large and the index is deep, each lookup may touch 3-4 random pages. With 100,000 outer rows that is 300,000-400,000 random I/Os — catastrophic on spinning disks and still expensive on SSDs.
BKA (Batched Key Access) changes the pattern: it collects a
batch of outer rows in join_buffer_size and hands all their join keys
to the storage engine at once as a Multi-Range Read (MRR). InnoDB looks
each key up in the secondary index, sorts the primary keys (row ids) it finds, and
then reads the rows in primary key order, so the reads move forward through the
table instead of jumping around.
When to enable it: BKA is not on by default. In MySQL you need:
SET optimizer_switch = 'batched_key_access=on,mrr=on,mrr_cost_based=off';
With mrr_cost_based=on (the default) MySQL skips Multi-Range Read
for a table it expects to find in memory, such as one smaller than the buffer
pool, and without Multi-Range Read there is no BKA. MySQL also never uses BKA for
lookups on InnoDB's clustered primary key, because its Multi-Range Read keeps
the one-by-one lookups there: the join needs a secondary index, as dept_no has
here. On MySQL 8.4 you can also set these in my.cnf globally.
The secondary index must also leave the query something to read from the
rows. When it covers every departments column the join needs (an index-only
read), MySQL and MariaDB both refuse the batched Multi-Range Read, and with it
BKA. Here d.name is not in the dept_no index, so the rows must be
read and BKA can sort those reads.
Why disk-order reads are faster: HDDs have ~10 ms seek time per random read. Sequential reads bypass seeks entirely — the head just streams. On SSDs, sequential reads still win because of read-ahead, fewer syscalls, and better NAND page utilization. A 10-50x speedup is common on HDD; 2-5x on SSD.
MariaDB supports BKA at join_cache_level 5 and 6 (7 and 8 add a
hashed join buffer, BKAH), with Multi-Range Read switched on:
SET join_cache_level = 6; SET optimizer_switch = 'mrr=on';. Its
default join_cache_level is 2, so BKA is off there too. The
principle is identical: batch, look up, sort the row ids, read in order.