<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h4 class="title">
      <a name="limit-optimization">
      </a>
      10.2.1.19 LIMIT Query Optimization
     </h4>
    </div>
   </div>
  </div>
  <a class="indexterm" name="idm46045228666976">
  </a>
  <a class="indexterm" name="idm46045228665520">
  </a>
  <p>
   If you need only a specified number of rows from a result set,
          use a
   <code class="literal">
    LIMIT
   </code>
   clause in the query, rather
          than fetching the whole result set and throwing away the extra
          data.
  </p>
  <p>
   MySQL sometimes optimizes a query that has a
   <code class="literal">
    LIMIT
    <em class="replaceable">
     <code>
      row_count
     </code>
    </em>
   </code>
   clause and no
   <code class="literal">
    HAVING
   </code>
   clause:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      If you select only a few rows with
      <code class="literal">
       LIMIT
      </code>
      , MySQL uses indexes in some cases
              when normally it would prefer to do a full table scan.
     </p>
    </li>
    <li class="listitem">
     <p>
      If you combine
      <code class="literal">
       LIMIT
       <em class="replaceable">
        <code>
         row_count
        </code>
       </em>
      </code>
      with
      <code class="literal">
       ORDER BY
      </code>
      , MySQL stops sorting as soon
              as it has found the first
      <em class="replaceable">
       <code>
        row_count
       </code>
      </em>
      rows of the sorted
              result, rather than sorting the entire result. If ordering
              is done by using an index, this is very fast. If a
              filesort must be done, all rows that match the query
              without the
      <code class="literal">
       LIMIT
      </code>
      clause are selected,
              and most or all of them are sorted, before the first
      <em class="replaceable">
       <code>
        row_count
       </code>
      </em>
      are found. After the
              initial rows have been found, MySQL does not sort any
              remainder of the result set.
     </p>
     <p>
      One manifestation of this behavior is that an
      <code class="literal">
       ORDER BY
      </code>
      query with and without
      <code class="literal">
       LIMIT
      </code>
      may return rows in different
              order, as described later in this section.
     </p>
    </li>
    <li class="listitem">
     <p>
      If you combine
      <code class="literal">
       LIMIT
       <em class="replaceable">
        <code>
         row_count
        </code>
       </em>
      </code>
      with
      <code class="literal">
       DISTINCT
      </code>
      , MySQL stops as soon as it
              finds
      <em class="replaceable">
       <code>
        row_count
       </code>
      </em>
      unique rows.
     </p>
    </li>
    <li class="listitem">
     <p>
      In some cases, a
      <code class="literal">
       GROUP BY
      </code>
      can be
              resolved by reading the index in order (or doing a sort on
              the index), then calculating summaries until the index
              value changes. In this case,
      <code class="literal">
       LIMIT
       <em class="replaceable">
        <code>
         row_count
        </code>
       </em>
      </code>
      does not
              calculate any unnecessary
      <code class="literal">
       GROUP BY
      </code>
      values.
     </p>
    </li>
    <li class="listitem">
     <p>
      As soon as MySQL has sent the required number of rows to
              the client, it aborts the query unless you are using
      <code class="literal">
       SQL_CALC_FOUND_ROWS
      </code>
      . In that case, the
              number of rows can be retrieved with
      <code class="literal">
       SELECT
              FOUND_ROWS()
      </code>
      . See
      <a class="xref" href="information-functions.html" title="14.15 Information Functions">
       Section 14.15, “Information Functions”
      </a>
      .
     </p>
     <a class="indexterm" name="idm46045228642368">
     </a>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       LIMIT 0
      </code>
      quickly returns an empty set.
              This can be useful for checking the validity of a query.
              It can also be employed to obtain the types of the result
              columns within applications that use a MySQL API that
              makes result set metadata available. With the
      <a class="link" href="mysql.html" title="6.5.1 mysql — The MySQL Command-Line Client">
       <span class="command">
        <strong>
         mysql
        </strong>
       </span>
      </a>
      client program, you can use the
      <a class="link" href="mysql-command-options.html#option_mysql_column-type-info">
       <code class="option">
        --column-type-info
       </code>
      </a>
      option to
              display result column types.
     </p>
    </li>
    <li class="listitem">
     <p>
      If the server uses temporary tables to resolve a query, it
              uses the
      <code class="literal">
       LIMIT
       <em class="replaceable">
        <code>
         row_count
        </code>
       </em>
      </code>
      clause to
              calculate how much space is required.
     </p>
    </li>
    <li class="listitem">
     <p>
      If an index is not used for
      <code class="literal">
       ORDER BY
      </code>
      but a
      <code class="literal">
       LIMIT
      </code>
      clause is also present, the
              optimizer may be able to avoid using a merge file and sort
              the rows in memory using an in-memory
      <code class="literal">
       filesort
      </code>
      operation.
     </p>
    </li>
   </ul>
  </div>
  <p>
   If multiple rows have identical values in the
   <code class="literal">
    ORDER
          BY
   </code>
   columns, the server is free to return those rows
          in any order, and may do so differently depending on the
          overall execution plan. In other words, the sort order of
          those rows is nondeterministic with respect to the nonordered
          columns.
  </p>
  <p>
   One factor that affects the execution plan is
   <code class="literal">
    LIMIT
   </code>
   , so an
   <code class="literal">
    ORDER BY
   </code>
   query with and without
   <code class="literal">
    LIMIT
   </code>
   may return
          rows in different orders. Consider this query, which is sorted
          by the
   <code class="literal">
    category
   </code>
   column but nondeterministic
          with respect to the
   <code class="literal">
    id
   </code>
   and
   <code class="literal">
    rating
   </code>
   columns:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa67527918"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token prompt">mysql&gt;</span> <span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> ratings <span class="token keyword">ORDER</span> <span class="token keyword">BY</span> category<span class="token punctuation">;</span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span> id <span class="token punctuation">|</span> category <span class="token punctuation">|</span> rating <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span>  1 <span class="token punctuation">|</span>        1 <span class="token punctuation">|</span>    4.5 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span>  5 <span class="token punctuation">|</span>        1 <span class="token punctuation">|</span>    3.2 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span>  3 <span class="token punctuation">|</span>        2 <span class="token punctuation">|</span>    3.7 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span>  4 <span class="token punctuation">|</span>        2 <span class="token punctuation">|</span>    3.5 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span>  6 <span class="token punctuation">|</span>        2 <span class="token punctuation">|</span>    3.5 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span>  2 <span class="token punctuation">|</span>        3 <span class="token punctuation">|</span>    5.0 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span>  7 <span class="token punctuation">|</span>        3 <span class="token punctuation">|</span>    2.7 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span></code></pre>
  </div>
  <p>
   Including
   <code class="literal">
    LIMIT
   </code>
   may affect order of rows
          within each
   <code class="literal">
    category
   </code>
   value. For example,
          this is a valid query result:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa77510867"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token prompt">mysql&gt;</span> <span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> ratings <span class="token keyword">ORDER</span> <span class="token keyword">BY</span> category <span class="token keyword">LIMIT</span> <span class="token number">5</span><span class="token punctuation">;</span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span> id <span class="token punctuation">|</span> category <span class="token punctuation">|</span> rating <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span>  1 <span class="token punctuation">|</span>        1 <span class="token punctuation">|</span>    4.5 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span>  5 <span class="token punctuation">|</span>        1 <span class="token punctuation">|</span>    3.2 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span>  4 <span class="token punctuation">|</span>        2 <span class="token punctuation">|</span>    3.5 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span>  3 <span class="token punctuation">|</span>        2 <span class="token punctuation">|</span>    3.7 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span>  6 <span class="token punctuation">|</span>        2 <span class="token punctuation">|</span>    3.5 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span></code></pre>
  </div>
  <p>
   In each case, the rows are sorted by the
   <code class="literal">
    ORDER
          BY
   </code>
   column, which is all that is required by the SQL
          standard.
  </p>
  <p>
   If it is important to ensure the same row order with and
          without
   <code class="literal">
    LIMIT
   </code>
   , include additional columns
          in the
   <code class="literal">
    ORDER BY
   </code>
   clause to make the order
          deterministic. For example, if
   <code class="literal">
    id
   </code>
   values
          are unique, you can make rows for a given
   <code class="literal">
    category
   </code>
   value appear in
   <code class="literal">
    id
   </code>
   order by sorting like this:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa1343208"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token prompt">mysql&gt;</span> <span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> ratings <span class="token keyword">ORDER</span> <span class="token keyword">BY</span> category<span class="token punctuation">,</span> id<span class="token punctuation">;</span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span> id <span class="token punctuation">|</span> category <span class="token punctuation">|</span> rating <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span>  1 <span class="token punctuation">|</span>        1 <span class="token punctuation">|</span>    4.5 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span>  5 <span class="token punctuation">|</span>        1 <span class="token punctuation">|</span>    3.2 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span>  3 <span class="token punctuation">|</span>        2 <span class="token punctuation">|</span>    3.7 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span>  4 <span class="token punctuation">|</span>        2 <span class="token punctuation">|</span>    3.5 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span>  6 <span class="token punctuation">|</span>        2 <span class="token punctuation">|</span>    3.5 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span>  2 <span class="token punctuation">|</span>        3 <span class="token punctuation">|</span>    5.0 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span>  7 <span class="token punctuation">|</span>        3 <span class="token punctuation">|</span>    2.7 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>

<span class="token prompt">mysql&gt;</span> <span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> ratings <span class="token keyword">ORDER</span> <span class="token keyword">BY</span> category<span class="token punctuation">,</span> id <span class="token keyword">LIMIT</span> <span class="token number">5</span><span class="token punctuation">;</span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span> id <span class="token punctuation">|</span> category <span class="token punctuation">|</span> rating <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span>  1 <span class="token punctuation">|</span>        1 <span class="token punctuation">|</span>    4.5 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span>  5 <span class="token punctuation">|</span>        1 <span class="token punctuation">|</span>    3.2 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span>  3 <span class="token punctuation">|</span>        2 <span class="token punctuation">|</span>    3.7 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span>  4 <span class="token punctuation">|</span>        2 <span class="token punctuation">|</span>    3.5 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span>  6 <span class="token punctuation">|</span>        2 <span class="token punctuation">|</span>    3.5 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span></code></pre>
  </div>
  <p>
   For a query with an
   <code class="literal">
    ORDER BY
   </code>
   or
   <code class="literal">
    GROUP BY
   </code>
   and a
   <code class="literal">
    LIMIT
   </code>
   clause, the optimizer tries to choose an ordered index by
          default when it appears doing so would speed up query
          execution. In cases where using some other optimization might
          be faster, it is possible to turn off this optimization by
          setting the
   <a class="link" href="server-system-variables.html#sysvar_optimizer_switch">
    <code class="literal">
     optimizer_switch
    </code>
   </a>
   system variable's
   <a class="link" href="switchable-optimizations.html#optflag_prefer-ordering-index">
    <code class="literal">
     prefer_ordering_index
    </code>
   </a>
   flag
          to
   <code class="literal">
    off
   </code>
   .
  </p>
  <p>
   <span class="emphasis">
    <em>
     Example
    </em>
   </span>
   : First we create and populate a
          table
   <code class="literal">
    t
   </code>
   as shown here:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa78822435"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token comment" spellcheck="true"># Create and populate a table t:</span>

<span class="token prompt">mysql&gt;</span> <span class="token keyword">CREATE</span> <span class="token keyword">TABLE</span> t <span class="token punctuation">(</span>
<span class="token prompt">    -&gt;</span>     id1 <span class="token datatype">BIGINT</span> <span class="token operator">NOT</span> <span class="token boolean">NULL</span><span class="token punctuation">,</span>
<span class="token prompt">    -&gt;</span>     id2 <span class="token datatype">BIGINT</span> <span class="token operator">NOT</span> <span class="token boolean">NULL</span><span class="token punctuation">,</span>
<span class="token prompt">    -&gt;</span>     c1 <span class="token datatype">VARCHAR</span><span class="token punctuation">(</span><span class="token number">50</span><span class="token punctuation">)</span> <span class="token operator">NOT</span> <span class="token boolean">NULL</span><span class="token punctuation">,</span>
<span class="token prompt">    -&gt;</span>     c2 <span class="token datatype">VARCHAR</span><span class="token punctuation">(</span><span class="token number">50</span><span class="token punctuation">)</span> <span class="token operator">NOT</span> <span class="token boolean">NULL</span><span class="token punctuation">,</span>
<span class="token prompt">    -&gt;</span>  <span class="token keyword">PRIMARY</span> <span class="token keyword">KEY</span> <span class="token punctuation">(</span>id1<span class="token punctuation">)</span><span class="token punctuation">,</span>
<span class="token prompt">    -&gt;</span>  <span class="token keyword">INDEX</span> i <span class="token punctuation">(</span>id2<span class="token punctuation">,</span> c1<span class="token punctuation">)</span>
<span class="token prompt">    -&gt;</span> <span class="token punctuation">)</span><span class="token punctuation">;</span>

<span class="token comment" spellcheck="true"># [Insert some rows into table t - not shown]</span></code></pre>
  </div>
  <p>
   Verify that the
   <a class="link" href="switchable-optimizations.html#optflag_prefer-ordering-index">
    <code class="literal">
     prefer_ordering_index
    </code>
   </a>
   flag
          is enabled:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa8189641"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token prompt">mysql&gt;</span> <span class="token keyword">SELECT</span> <span class="token variable">@@optimizer_switch</span> <span class="token operator">LIKE</span> <span class="token string">'%prefer_ordering_index=on%'</span><span class="token punctuation">;</span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span> @@optimizer_switch LIKE '%prefer_ordering_index=on%' <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span>                                                    1 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span></code></pre>
  </div>
  <p>
   Since the following query has a
   <code class="literal">
    LIMIT
   </code>
   clause, we expect it to use an ordered index, if possible. In
          this case, as we can see from the
   <a class="link" href="explain.html" title="15.8.2 EXPLAIN Statement">
    <code class="literal">
     EXPLAIN
    </code>
   </a>
   output, it uses the
          table's primary key.
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa81787230"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token prompt">mysql&gt;</span> <span class="token keyword">EXPLAIN</span> <span class="token keyword">SELECT</span> c2 <span class="token keyword">FROM</span> t
<span class="token prompt">    -&gt;</span>     <span class="token keyword">WHERE</span> id2 <span class="token operator">&gt;</span> <span class="token number">3</span>
<span class="token prompt">    -&gt;</span>     <span class="token keyword">ORDER</span> <span class="token keyword">BY</span> id1 <span class="token keyword">ASC</span> <span class="token keyword">LIMIT</span> <span class="token number">2</span>\G
<span class="token output"><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span> 1. row <span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span>
           id<span class="token punctuation">:</span> 1
  select_type<span class="token punctuation">:</span> SIMPLE
        table<span class="token punctuation">:</span> t
   partitions<span class="token punctuation">:</span> NULL
         type<span class="token punctuation">:</span> index
possible_keys<span class="token punctuation">:</span> i
          key<span class="token punctuation">:</span> PRIMARY
      key_len<span class="token punctuation">:</span> 8
          ref<span class="token punctuation">:</span> NULL
         rows<span class="token punctuation">:</span> 2
     filtered<span class="token punctuation">:</span> 70.00
        Extra<span class="token punctuation">:</span> Using where</span></code></pre>
  </div>
  <p>
   Now we disable the
   <a class="link" href="switchable-optimizations.html#optflag_prefer-ordering-index">
    <code class="literal">
     prefer_ordering_index
    </code>
   </a>
   flag,
          and re-run the same query; this time it uses the index
   <code class="literal">
    i
   </code>
   (which includes the
   <code class="literal">
    id2
   </code>
   column used in the
   <code class="literal">
    WHERE
   </code>
   clause), and a filesort:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa80063034"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token prompt">mysql&gt;</span> <span class="token keyword">SET</span> optimizer_switch <span class="token operator">=</span> <span class="token string">"prefer_ordering_index=off"</span><span class="token punctuation">;</span>

<span class="token prompt">mysql&gt;</span> <span class="token keyword">EXPLAIN</span> <span class="token keyword">SELECT</span> c2 <span class="token keyword">FROM</span> t
<span class="token prompt">    -&gt;</span>     <span class="token keyword">WHERE</span> id2 <span class="token operator">&gt;</span> <span class="token number">3</span>
<span class="token prompt">    -&gt;</span>     <span class="token keyword">ORDER</span> <span class="token keyword">BY</span> id1 <span class="token keyword">ASC</span> <span class="token keyword">LIMIT</span> <span class="token number">2</span>\G
<span class="token output"><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span> 1. row <span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span>
           id<span class="token punctuation">:</span> 1
  select_type<span class="token punctuation">:</span> SIMPLE
        table<span class="token punctuation">:</span> t
   partitions<span class="token punctuation">:</span> NULL
         type<span class="token punctuation">:</span> range
possible_keys<span class="token punctuation">:</span> i
          key<span class="token punctuation">:</span> i
      key_len<span class="token punctuation">:</span> 8
          ref<span class="token punctuation">:</span> NULL
         rows<span class="token punctuation">:</span> 14
     filtered<span class="token punctuation">:</span> 100.00
        Extra<span class="token punctuation">:</span> Using index condition; Using filesort</span></code></pre>
  </div>
  <p>
   See also
   <a class="xref" href="switchable-optimizations.html" title="10.9.2 Switchable Optimizations">
    Section 10.9.2, “Switchable Optimizations”
   </a>
   .
  </p>
 </div>
 <br/>
</div>
