Multi Range Read optimization
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
The idea of Multi Range Read optimization is that index-based data access can be faster when done in big batches. Normally, SQL engine would
((TODO: pic with regular execution vs MRR execution ))
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
", orKey-ordered Rowid-ordered scan".
See also
- What is MariaDB 5.3
- Multi-Range Read Optimization page in MySQL manual