MySQL Multi-Range Read (MRR) Optimization: Advantages, Limitations, Use Cases, and Configuration

Advantages of Multi-Range Read Optimization

Sequential data access: Based on index tuples, MRR enables rows to be accessed in order rather than randomly, improving efficiency and cost.

Batch processing of key access requests: For operations that need to fetch rows via index tuples (e.g., range scans or equality joins using index columns), MRR can batch the key requests, accumulating index tuples and accessing rows without having to fetch all tuples beforehand.

Limitations of Multi-Range Read Optimization

MRR does not support secondary indexes created on virtual generated columns; InnoDB does support such indexes.

Applicable Scenarios

Scenario A: Suitable for index range scans and equality joins on InnoDB and MyISAM tables.

Part of the index tuples are accumulated in a buffer.

The tuples in the buffer are sorted by row ID.

Rows are accessed in the order of the sorted index tuples.

Scenario B: Suitable for multi-range index scans or equality joins on NDB tables.

A portion of the ranges (possibly single-key ranges) is accumulated on the query‑issuing central node.

The ranges are sent to the execution nodes that access the rows.

Accessed rows are packed into packets and sent back to the central node.

The received packets are placed in a buffer.

Rows are read from the buffer.

When using MRR, the EXPLAIN output shows Using MRR in the Extra column.

Usage Conditions

If the query result can be generated entirely from the information in the index tuples (i.e., via a covering index), InnoDB and MyISAM will not use MRR because it offers no benefit.

Configuration of Multi-Range Read Optimization

Two optimizer_switch system‑variable flags control MRR usage:

mrr flag enables MRR. When mrr is on , the mrr_cost_based flag determines whether the optimizer selects MRR based on cost ( on ) or uses it whenever possible ( off ).

By default, mrr is on and mrr_cost_based is on . See the “Switchable Optimizations” section for more details.

Storage Engine Buffer Settings for MRR

The storage engine uses the read_rnd_buffer_size system variable as a guide for the amount of memory allocated to its buffer. The engine may use up to read_rnd_buffer_size bytes and determines the number of ranges processed per batch based on this value.