Skip navigation links

Additional languages
7.2.10 IS NULL Optimization »
Section Navigation      [Toggle]

7.2.9. Multi Read Range Optimization

The Multi Read Range (MRR) optimization provides these benefits:

  • MRR enables data rows to be accessed sequentially rather than in random order, based on index tuples. The server obtains a set of index tuples that satisfy the query conditions, sorts them according to data row ID order, and uses the sorted tuples to retrieve data rows in order. This makes data access more efficient and less expensive.

  • MRR enables batch processing of requests for key access for operations that require access to data rows via index tuples, such as range index scans and equi-joins that use an index for the join attribute. MRR iterates over a sequence of index ranges to obtain qualifying index tuples. As these results accumulate, they are used to access the corresponding data records. It is not necessary to acquire all index tuples before starting to read data rows.

The following scenarios illustrate when MRR optimization can be advantageous:

Scenario A: MRR can be used for MyISAM tables for index range scans and equi-join operations.

  1. A portion of the index tuples are accumulated in a buffer.

  2. The tuples in the buffer are sorted by their data row ID.

  3. Data rows are accessed according to the sorted index tuple sequence.

Scenario B: MRR can be used for NDB tables for multiple-range index scans or when performing an equi-join by an attribute.

  1. A portion of ranges, possibly single-key ranges, is accumulated in a buffer on the central node where the query is submitted.

  2. The ranges are sent to the execution nodes that access data records.

  3. The accessed records are packed into packages and sent back to the central node.

  4. The received packages with data records are placed in a buffer.

  5. Data records are read from the buffer.

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

MyISAM does not use MRR if full table records need not be accessed to produce the query result. This is the case if results can be produced entirely on the basis on information in the index tuples; MRR provides no benefit.

Example query for which MRR can be used, assuming that there is an index on (key_part1, key_part2): 

SELECT * FROM t
  WHERE key_part1 >= 1000 AND key_part1 < 2000
  AND key_part2 = 10000;

The index consists of tuples of (key_part1, key_part2) values, ordered first by key_part1 and then by key_part2.

Without MRR, an index scan covers all index tuples for the key_part1 range from 1000 up to 2000, regardless of the key_part2 value in these tuples. The scan does extra work to the extent that tuples in the range contain key_part2 values other than 10000.

With MRR, the scan is broken up into multiple ranges, each for a single value of key_part1 (1000, 1001, ... , 1999). Each of these scans need look only for tuples with key_part2 = 10000. If the index contains many tuples for which key_part2 is not 10000, MRR results in many fewer index tuples being read.

To express this using interval notation, the non-MRR scan must examine the index range [{1000, 10000}, {2000, MIN_INT}), which may include many tuples other than those for which key_part2 = 10000. The MRR scan examines multiple single-point intervals [{1000, 10000}], ..., [{1999, 10000}], which includes only tuples with key_part2 = 10000.

The optimizer_use_mrr system variable provides an interface to the use of MRR optimization. It has supported values of force and disable. The default value is force. That is, MRR is used whenever it is applicable.

For MRR, a storage engine uses the value of the read_rnd_buffer_size system variable as a guideline for how much memory it can allocate for its buffer. The engine uses up to read_rnd_buffer_size bytes and determines the number of ranges to process in a single pass.