Optimizing SELECT Statements The Multi-Range Read optimization provides these benefits: The MRR optimization is not supported with secondary indexes created on virtual generated columns. InnoDB  supports secondary indexes on virtual generated columns. The following scenarios illustrate when MRR optimization can be advantageous: Scenario A: MRR can be used for  InnoDB  and  MyISAM  tables for index range scans and equi-join operations. Scenario B: MRR can be used for  NDB  tables for multiple-range index scans or when performing an equi- join by an attribute. When MRR is used, the  Extra  column in  EXPLAIN  output shows  Using MRR . InnoDB  and  MyISAM  do not use MRR if full table rows 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 (through a covering index); MRR provides no benefit. Two  optimizer_switch  system variable flags provide an interface to the use of MRR optimization. The  mrr  flag controls whether MRR is enabled. If  mrr  is enabled ( on ), the  mrr_cost_based  flag controls whether the optimizer attempts to make a cost-based choice between using and not using MRR ( on ) or uses MRR whenever possible ( off ). By default,  mrr  is  on  and  mrr_cost_based  is  on . See Section 8.9.2, “Switchable Optimizations” . 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. 1671 8.2.1.12 Block Nested-Loop and Batched Key Access Joins •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 through 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 rows. It is not necessary to acquire all index tuples before starting to read data rows. 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. 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 rows. 3. The accessed rows are packed into packages and sent back to the central node. 4. The received packages with data rows are placed in a buffer. 5. Data rows are read from the buffer.