How MySQL’s Multi-Range Read (MRR) Turns Random I/O into Sequential I/O

MRR Overview

MRR (Multi‑Range Read) is a MySQL optimization that reduces random disk accesses by converting them into sequential I/O, especially for queries with range predicates (e.g., BETWEEN, <, >) that require a secondary‑index lookup followed by a table‑row fetch.

Background

In InnoDB, table rows are stored in a clustered primary key. When a range query uses a secondary index, MySQL first finds matching index entries, then performs a back‑table lookup for each primary key, causing many random I/O operations on large tables.

Principle

Scan secondary index and collect primary keys : MySQL reads the secondary‑index entries that satisfy the range condition and stores the corresponding primary‑key values (rowids) in an in‑memory buffer ( read_rnd_buffer).

Sort primary keys : When the buffer is full or the query finishes, the collected primary keys are sorted. Sorting enables sequential access to the base table.

Sequentially access the base table : The sorted primary keys are used to read the rows in order, turning random I/O into sequential I/O.

Use disk pre‑read and cache : Because accesses are now sequential, the disk’s pre‑read mechanism can fetch adjacent pages in advance, reducing seek time and improving cache hit rates.

Advantages

Improved query performance : Fewer random I/O operations and higher cache hit rates lead to noticeable speedups, especially on large tables.

Lower I/O cost : Sequential I/O utilizes disk bandwidth more efficiently than random I/O.

Broad applicability : Works for range queries as well as equi‑joins that require row lookups.

Disk Pre‑Read Mechanism

When a page is requested, the storage subsystem predicts and reads neighboring pages into memory. Since MRR makes accesses sequential, the pre‑read feature can fetch the next pages proactively, further reducing latency.

Locality Principle

MRR aligns with temporal and spatial locality: ordered accesses increase the likelihood that recently accessed data or nearby data are already in cache, reducing overall I/O.

Use Cases and Conditions

MRR is most effective for range scans and equi‑joins that involve a back‑table lookup. It provides no benefit when a covering index satisfies the query because no row fetch is needed.

MySQL enables MRR by default, but the optimizer decides whether to apply it based on cost estimates. Users can influence the decision via the optimizer_switch system variable, toggling the mrr and mrr_cost_based flags.

Configuration Parameters

optimizer_switch

: contains mrr and mrr_cost_based to enable the feature and to make it cost‑based. read_rnd_buffer_size: sets the size of the in‑memory buffer used for sorting primary keys; larger values can improve MRR effectiveness.

Monitoring Methods

Run EXPLAIN and look for “Using MRR” in the Extra column.

Compare query latency and I/O statistics with MRR enabled and disabled.

SQL Example

Table definition:

CREATE TABLE orders (
    id INT AUTO_INCREMENT PRIMARY KEY,
    customer_id INT NOT NULL,
    order_date DATE NOT NULL,
    total_amount DECIMAL(10,2) NOT NULL,
    INDEX idx_customer_date (customer_id, order_date)
) ENGINE=InnoDB;

Query to fetch orders for a specific customer within a date range:

SELECT * FROM orders
WHERE customer_id = 123
  AND order_date BETWEEN '2024-09-25' AND '2024-09-26';

Scan secondary index : MySQL uses idx_customer_date to locate matching index tuples for the specified customer_id and date range.

Collect and sort primary keys : The primary keys from those tuples are stored in read_rnd_buffer and sorted when the buffer is full or the query ends.

Sequentially access the base table : The sorted primary keys drive ordered reads of the orders table, turning random I/O into sequential I/O.

Leverage disk pre‑read and cache : Sequential reads trigger the storage pre‑read mechanism, reducing seek time and improving cache hits.

Performance gain : Compared with a non‑MRR execution, the number of random I/O operations drops dramatically, especially on large tables.