Multi Range Read optimization

Multi Range Read is an optimization aimed at improving performance for IO-bound queries that need to scan lots of rows.

In MariaDB, it is available since MariaDB 5.3, main-line MySQL has a subset of it available in MySQL 5.6.

The idea

Consider a simple range query:

MariaDB [test]> explain select * from tbl where tbl.key1 between 1000 and 2000;
+----+-------------+-------+-------+---------------+------+---------+------+------+-----------------------+
| id | select_type | table | type  | possible_keys | key  | key_len | ref  | rows | Extra                 |
+----+-------------+-------+-------+---------------+------+---------+------+------+-----------------------+
|  1 | SIMPLE      | tbl   | range | key1          | key1 | 5       | NULL |  960 | Using index condition |
+----+-------------+-------+-------+---------------+------+---------+------+------+-----------------------+

This query will be executed as follows:

If the table is big enough

Factsheet

Old stuff

Multi Range Read optimization provides speedup for range, ref, or eq_ref access when

• each query enumerates lots of records (the range/ref access in question should read about 100K records or more)
• the load is mostly IO-bound. In other words, there is a high chance that the query will hit the data that's not in disk cache or buffer pool.

Batched Key Access optimization uses MultiRangeRead functionality, so Multi Range Read must be enabled for Batched Key Access to work.

Because Multi Range Read can cause slowdowns when ran for small ranges over small tables, it is disabled by default. To enable it, use the following settings:

SET optimizer_switch='mrr=on,mrr_sort_keys=on,mrr_cost_based=off'

Switching on mrr_cost_based is currently not recommended because cost model is not sufficiently well-tuned to be practically useful.

How Multi Range Read works

Differences from MySQL

• Key-ordered scan is a MariaDB-only development
• EXPLAIN in MySQL shows "Using MRR", while in MariaDB it is either "Rowid-ordered scan", "Key-ordered scan", or Key-ordered Rowid-ordered scan".
• for range access, the size of the buffer is controlled by @mrr_buffer_size variable (Original MySQL 6.0 and current MySQL 5.6 use @@read_rnd_buffer_size, which is also used to control buffer size for other algorithms)
• [is this a difference?] When used with BKA, buffer space management is done by BKA code. It uses up to @join_buffer_size bytes per table, distributing it across its own buffers and buffers it provides to MRR.

See also

• What is MariaDB 5.3
• Multi-Range Read Optimization page in MySQL manual