<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h4 class="title">
      <a name="mrr-optimization">
      </a>
      10.2.1.11 Multi-Range Read Optimization
     </h4>
    </div>
   </div>
  </div>
  <a class="indexterm" name="idm46045229324672">
  </a>
  <a class="indexterm" name="idm46045229323216">
  </a>
  <p>
   Reading rows using a range scan on a secondary index can
          result in many random disk accesses to the base table when the
          table is large and not stored in the storage engine's
          cache. With the Disk-Sweep Multi-Range Read (MRR)
          optimization, MySQL tries to reduce the number of random disk
          access for range scans by first scanning the index only and
          collecting the keys for the relevant rows. Then the keys are
          sorted and finally the rows are retrieved from the base table
          using the order of the primary key. The motivation for
          Disk-sweep MRR is to reduce the number of random disk accesses
          and instead achieve a more sequential scan of the base table
          data.
  </p>
  <p>
   The Multi-Range Read optimization provides these benefits:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      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.
     </p>
    </li>
    <li class="listitem">
     <p>
      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.
     </p>
    </li>
   </ul>
  </div>
  <p>
   The MRR optimization is not supported with secondary indexes
          created on virtual generated columns.
   <code class="literal">
    InnoDB
   </code>
   supports secondary indexes on
          virtual generated columns.
  </p>
  <p>
   The following scenarios illustrate when MRR optimization can
          be advantageous:
  </p>
  <p>
   Scenario A: MRR can be used for
   <code class="literal">
    InnoDB
   </code>
   and
   <code class="literal">
    MyISAM
   </code>
   tables for index range scans and
          equi-join operations.
  </p>
  <div class="orderedlist">
   <ol class="orderedlist" type="1">
    <li class="listitem">
     <p>
      A portion of the index tuples are accumulated in a buffer.
     </p>
    </li>
    <li class="listitem">
     <p>
      The tuples in the buffer are sorted by their data row ID.
     </p>
    </li>
    <li class="listitem">
     <p>
      Data rows are accessed according to the sorted index tuple
              sequence.
     </p>
    </li>
   </ol>
  </div>
  <p>
   Scenario B: MRR can be used for
   <a class="link" href="mysql-cluster.html" title="Chapter 25 MySQL NDB Cluster 8.4">
    <code class="literal">
     NDB
    </code>
   </a>
   tables for multiple-range
          index scans or when performing an equi-join by an attribute.
  </p>
  <div class="orderedlist">
   <ol class="orderedlist" type="1">
    <li class="listitem">
     <p>
      A portion of ranges, possibly single-key ranges, is
              accumulated in a buffer on the central node where the
              query is submitted.
     </p>
    </li>
    <li class="listitem">
     <p>
      The ranges are sent to the execution nodes that access
              data rows.
     </p>
    </li>
    <li class="listitem">
     <p>
      The accessed rows are packed into packages and sent back
              to the central node.
     </p>
    </li>
    <li class="listitem">
     <p>
      The received packages with data rows are placed in a
              buffer.
     </p>
    </li>
    <li class="listitem">
     <p>
      Data rows are read from the buffer.
     </p>
    </li>
   </ol>
  </div>
  <p>
   When MRR is used, the
   <code class="literal">
    Extra
   </code>
   column in
   <a class="link" href="explain.html" title="15.8.2 EXPLAIN Statement">
    <code class="literal">
     EXPLAIN
    </code>
   </a>
   output shows
   <code class="literal">
    Using MRR
   </code>
   .
  </p>
  <p>
   <code class="literal">
    InnoDB
   </code>
   and
   <code class="literal">
    MyISAM
   </code>
   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
   <a class="link" href="glossary.html#glos_covering_index" title="covering index">
    covering
          index
   </a>
   ); MRR provides no benefit.
  </p>
  <p>
   Two
   <a class="link" href="server-system-variables.html#sysvar_optimizer_switch">
    <code class="literal">
     optimizer_switch
    </code>
   </a>
   system
          variable flags provide an interface to the use of MRR
          optimization. The
   <a class="link" href="switchable-optimizations.html#optflag_mrr">
    <code class="literal">
     mrr
    </code>
   </a>
   flag
          controls whether MRR is enabled. If
   <a class="link" href="switchable-optimizations.html#optflag_mrr">
    <code class="literal">
     mrr
    </code>
   </a>
   is enabled
          (
   <code class="literal">
    on
   </code>
   ), the
   <a class="link" href="switchable-optimizations.html#optflag_mrr-cost-based">
    <code class="literal">
     mrr_cost_based
    </code>
   </a>
   flag controls
          whether the optimizer attempts to make a cost-based choice
          between using and not using MRR (
   <code class="literal">
    on
   </code>
   ) or
          uses MRR whenever possible (
   <code class="literal">
    off
   </code>
   ). By
          default,
   <a class="link" href="switchable-optimizations.html#optflag_mrr">
    <code class="literal">
     mrr
    </code>
   </a>
   is
   <code class="literal">
    on
   </code>
   and
   <a class="link" href="switchable-optimizations.html#optflag_mrr-cost-based">
    <code class="literal">
     mrr_cost_based
    </code>
   </a>
   is
   <code class="literal">
    on
   </code>
   . See
   <a class="xref" href="switchable-optimizations.html" title="10.9.2 Switchable Optimizations">
    Section 10.9.2, “Switchable Optimizations”
   </a>
   .
  </p>
  <p>
   For MRR, a storage engine uses the value of the
   <a class="link" href="server-system-variables.html#sysvar_read_rnd_buffer_size">
    <code class="literal">
     read_rnd_buffer_size
    </code>
   </a>
   system
          variable as a guideline for how much memory it can allocate
          for its buffer. The engine uses up to
   <a class="link" href="server-system-variables.html#sysvar_read_rnd_buffer_size">
    <code class="literal">
     read_rnd_buffer_size
    </code>
   </a>
   bytes
          and determines the number of ranges to process in a single
          pass.
  </p>
 </div>
 <br/>
</div>
