Understanding and Optimizing MySQL Multi-Range Read (MRR)

MySQL's Multi-Range Read (MRR) is an optimization that sorts primary key values before the table lookup to replace random I/O with sequential I/O when a secondary index is used.

To test MRR, a sample table t is created with several integer and varchar columns and an index on i0 . Data is populated and i0 is set to id % 100 .

When MRR is enabled, an EXPLAIN of SELECT i0,i3 FROM t WHERE i0 BETWEEN 1 AND 2 shows Using index condition; Using MRR and returns 22,400 rows in 0.80 s. After disabling MRR ( mrr=off ) the same query still uses the index condition but takes 2.56 s, demonstrating roughly a three‑fold speed improvement.

The optimizer switch controls MRR behavior. Enabling it with set optimizer_switch = '... ,mrr=on,mrr_cost_based=off,...' forces MRR regardless of cost‑based decisions, while mrr_cost_based=on lets the optimizer decide. Experiments show that keeping mrr_cost_based=on yields the best performance (1.52 s vs 4.86 s for the same query range).

MRR relies on sufficient memory for sorting, governed by read_rnd_buffer_size . The default 256 KB may be too small; increasing it (e.g., set read_rnd_buffer_size = 32*1024*1024 ) provides enough buffer for MRR to operate efficiently.

In summary, enabling MRR and ensuring read_rnd_buffer_size is large enough can improve query performance by up to three times, but the cost‑based switch should generally remain on to avoid regressions on queries where full table scans are faster.