MySQL Multi-Range Read Optimization Explained

This article explains how MySQL utilizes the Multi-Range Read (MRR) optimization to enhance query performance and disk I/O efficiency. We will explore the underlying mechanism of MRR, how it transitions random disk reads into sequential ones using a memory buffer, and the specific scenarios where this database optimization provides the greatest performance benefits.

The Problem with Traditional Index Lookups

When MySQL executes a query using a secondary index, it must perform a two-step process to retrieve the requested data. First, it searches the secondary index to find the matching index entries, which contain the primary keys (row IDs). Second, it uses those primary keys to fetch the actual data rows from the clustered index (the table space).

Because secondary indexes are rarely ordered the same way as the physical table data, retrieving rows in the order they appear in the secondary index results in highly fragmented, random disk I/O. The database engine ends up jumping back and forth across different disk pages, sometimes reading the same data page multiple times, which severely degrades query performance on disk-bound workloads.

How Multi-Range Read (MRR) Optimizes I/O

The Multi-Range Read optimization solves this disk I/O bottleneck by buffering and sorting the row lookups before accessing the table space. Instead of fetching rows immediately as they are found in the secondary index, MRR restructures the retrieval process into three key phases:

1. Buffering Row IDs: As MySQL scans the secondary index, it collects the primary keys of the qualified rows and stores them in a memory buffer, known as the read_rnd_buffer_size.
2. Sorting by Physical Location: Once the buffer is full (or the index scan is complete), MySQL sorts the accumulated primary keys in ascending order. Because the clustered index is physically ordered by the primary key, this sort aligns the keys with their exact physical layout on the storage disk.
3. Sequential Data Retrieval: MySQL retrieves the rows from the table space sequentially using the sorted keys.

By restructuring the retrieval order, MRR effectively converts expensive, random disk access into highly efficient, sequential disk access.

Key Benefits of MRR

By executing sequential reads instead of random reads, MRR provides several architectural advantages:

• Reduced Disk Head Movement: For traditional spinning hard drives (HDDs), sequential access minimizes physical disk head movement. For Solid State Drives (SSDs), it optimizes parallel block operations and controller queue efficiency.
• Minimized Page Reads: Because the lookups are sorted, all required rows residing on the same data page are retrieved in a single pass. This prevents the database from repeatedly loading and evicting the same data pages from the InnoDB buffer pool.
• Improved Cache and Read-Ahead Efficiency: Operating systems and storage engines are optimized for sequential access. Sorting the reads allows the system to leverage hardware read-ahead algorithms, pre-fetching data blocks into memory before they are explicitly requested.

When MySQL Triggers MRR

MySQL generally applies MRR during range scans, ref joins, and eq_ref joins on secondary indexes when it determines that a significant number of row lookups will be required. The optimizer evaluates the cost of using MRR versus a traditional lookup and will automatically enable it if it estimates that the disk-read savings outweigh the CPU overhead of sorting the keys in memory.