<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h4 class="title">
      <a name="order-by-optimization">
      </a>
      10.2.1.16 ORDER BY Optimization
     </h4>
    </div>
   </div>
  </div>
  <a class="indexterm" name="idm46045228990736">
  </a>
  <a class="indexterm" name="idm46045228989664">
  </a>
  <p>
   This section describes when MySQL can use an index to satisfy
          an
   <code class="literal">
    ORDER BY
   </code>
   clause, the
   <code class="literal">
    filesort
   </code>
   operation used when an index
          cannot be used, and execution plan information available from
          the optimizer about
   <code class="literal">
    ORDER BY
   </code>
   .
  </p>
  <p>
   An
   <code class="literal">
    ORDER BY
   </code>
   with and without
   <code class="literal">
    LIMIT
   </code>
   may return rows in different orders,
          as discussed in
   <a class="xref" href="limit-optimization.html" title="10.2.1.19 LIMIT Query Optimization">
    Section 10.2.1.19, “LIMIT Query Optimization”
   </a>
   .
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      <a class="xref" href="order-by-optimization.html#order-by-index-use" title="Use of Indexes to Satisfy ORDER BY">
       Use of Indexes to Satisfy ORDER BY
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="xref" href="order-by-optimization.html#order-by-filesort" title="Use of filesort to Satisfy ORDER BY">
       Use of filesort to Satisfy ORDER BY
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="xref" href="order-by-optimization.html#order-by-optimizer-control" title="Influencing ORDER BY Optimization">
       Influencing ORDER BY Optimization
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="xref" href="order-by-optimization.html#order-by-diagnostic-information" title="ORDER BY Execution Plan Information Available">
       ORDER BY Execution Plan Information Available
      </a>
     </p>
    </li>
   </ul>
  </div>
  <div class="simplesect">
   <div class="titlepage">
    <div>
     <div class="simple">
      <h5 class="title">
       <a name="order-by-index-use">
       </a>
       Use of Indexes to Satisfy ORDER BY
      </h5>
     </div>
    </div>
   </div>
   <p>
    In some cases, MySQL may use an index to satisfy an
    <code class="literal">
     ORDER BY
    </code>
    clause and avoid the extra
            sorting involved in performing a
    <code class="literal">
     filesort
    </code>
    operation.
   </p>
   <p>
    The index may also be used even if the
    <code class="literal">
     ORDER
            BY
    </code>
    does not match the index exactly, as long as
            all unused portions of the index and all extra
    <code class="literal">
     ORDER BY
    </code>
    columns are constants in the
    <code class="literal">
     WHERE
    </code>
    clause. If the index does not
            contain all columns accessed by the query, the index is used
            only if index access is cheaper than other access methods.
   </p>
   <p>
    Assuming that there is an index on
    <code class="literal">
     (
     <em class="replaceable">
      <code>
       key_part1
      </code>
     </em>
     ,
     <em class="replaceable">
      <code>
       key_part2
      </code>
     </em>
     )
    </code>
    , the
            following queries may use the index to resolve the
    <code class="literal">
     ORDER BY
    </code>
    part. Whether the optimizer
            actually does so depends on whether reading the index is
            more efficient than a table scan if columns not in the index
            must also be read.
   </p>
   <div class="itemizedlist">
    <ul class="itemizedlist" style="list-style-type: disc; ">
     <li class="listitem">
      <p>
       In this query, the index on
       <code class="literal">
        (
        <em class="replaceable">
         <code>
          key_part1
         </code>
        </em>
        ,
        <em class="replaceable">
         <code>
          key_part2
         </code>
        </em>
        )
       </code>
       enables
                the optimizer to avoid sorting:
      </p>
      <div class="copytoclipboard-wrapper">
       <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa89495672"><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 keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> t1
  <span class="token keyword">ORDER</span> <span class="token keyword">BY</span> <em class="replaceable">key_part1</em><span class="token punctuation">,</span> <em class="replaceable">key_part2</em><span class="token punctuation">;</span></code></pre>
      </div>
      <p>
       However, the query uses
       <code class="literal">
        SELECT *
       </code>
       ,
                which may select more columns than
       <em class="replaceable">
        <code>
         key_part1
        </code>
       </em>
       and
       <em class="replaceable">
        <code>
         key_part2
        </code>
       </em>
       . In that case,
                scanning an entire index and looking up table rows to
                find columns not in the index may be more expensive than
                scanning the table and sorting the results. If so, the
                optimizer probably does not use the index. If
       <code class="literal">
        SELECT *
       </code>
       selects only the index
                columns, the index is used and sorting avoided.
      </p>
      <p>
       If
       <code class="literal">
        t1
       </code>
       is an
       <code class="literal">
        InnoDB
       </code>
       table, the table primary key is implicitly part of the
                index, and the index can be used to resolve the
       <code class="literal">
        ORDER BY
       </code>
       for this query:
      </p>
      <div class="copytoclipboard-wrapper">
       <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa50965856"><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 keyword">SELECT</span> <em class="replaceable">pk</em><span class="token punctuation">,</span> <em class="replaceable">key_part1</em><span class="token punctuation">,</span> <em class="replaceable">key_part2</em> <span class="token keyword">FROM</span> t1
  <span class="token keyword">ORDER</span> <span class="token keyword">BY</span> <em class="replaceable">key_part1</em><span class="token punctuation">,</span> <em class="replaceable">key_part2</em><span class="token punctuation">;</span></code></pre>
      </div>
     </li>
     <li class="listitem">
      <p>
       In this query,
       <em class="replaceable">
        <code>
         key_part1
        </code>
       </em>
       is
                constant, so all rows accessed through the index are in
       <em class="replaceable">
        <code>
         key_part2
        </code>
       </em>
       order, and an index
                on
       <code class="literal">
        (
        <em class="replaceable">
         <code>
          key_part1
         </code>
        </em>
        ,
        <em class="replaceable">
         <code>
          key_part2
         </code>
        </em>
        )
       </code>
       avoids
                sorting if the
       <code class="literal">
        WHERE
       </code>
       clause is
                selective enough to make an index range scan cheaper
                than a table scan:
      </p>
      <div class="copytoclipboard-wrapper">
       <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa6010430"><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 keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> t1
  <span class="token keyword">WHERE</span> <em class="replaceable">key_part1</em> <span class="token operator">=</span> <em class="replaceable">constant</em>
  <span class="token keyword">ORDER</span> <span class="token keyword">BY</span> <em class="replaceable">key_part2</em><span class="token punctuation">;</span></code></pre>
      </div>
     </li>
     <li class="listitem">
      <p>
       In the next two queries, whether the index is used is
                similar to the same queries without
       <code class="literal">
        DESC
       </code>
       shown previously:
      </p>
      <div class="copytoclipboard-wrapper">
       <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa50470115"><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 keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> t1
  <span class="token keyword">ORDER</span> <span class="token keyword">BY</span> <em class="replaceable">key_part1</em> <span class="token keyword">DESC</span><span class="token punctuation">,</span> <em class="replaceable">key_part2</em> <span class="token keyword">DESC</span><span class="token punctuation">;</span>

<span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> t1
  <span class="token keyword">WHERE</span> <em class="replaceable">key_part1</em> <span class="token operator">=</span> <em class="replaceable">constant</em>
  <span class="token keyword">ORDER</span> <span class="token keyword">BY</span> <em class="replaceable">key_part2</em> <span class="token keyword">DESC</span><span class="token punctuation">;</span></code></pre>
      </div>
     </li>
     <li class="listitem">
      <p>
       Two columns in an
       <code class="literal">
        ORDER BY
       </code>
       can sort
                in the same direction (both
       <code class="literal">
        ASC
       </code>
       , or
                both
       <code class="literal">
        DESC
       </code>
       ) or in opposite directions
                (one
       <code class="literal">
        ASC
       </code>
       , one
       <code class="literal">
        DESC
       </code>
       ). A condition for index use is
                that the index must have the same homogeneity, but need
                not have the same actual direction.
      </p>
      <p>
       If a query mixes
       <code class="literal">
        ASC
       </code>
       and
       <code class="literal">
        DESC
       </code>
       , the optimizer can use an index
                on the columns if the index also uses corresponding
                mixed ascending and descending columns:
      </p>
      <div class="copytoclipboard-wrapper">
       <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa8499702"><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 keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> t1
  <span class="token keyword">ORDER</span> <span class="token keyword">BY</span> <em class="replaceable">key_part1</em> <span class="token keyword">DESC</span><span class="token punctuation">,</span> <em class="replaceable">key_part2</em> <span class="token keyword">ASC</span><span class="token punctuation">;</span></code></pre>
      </div>
      <p>
       The optimizer can use an index on
                (
       <em class="replaceable">
        <code>
         key_part1
        </code>
       </em>
       ,
       <em class="replaceable">
        <code>
         key_part2
        </code>
       </em>
       ) if
       <em class="replaceable">
        <code>
         key_part1
        </code>
       </em>
       is descending and
       <em class="replaceable">
        <code>
         key_part2
        </code>
       </em>
       is ascending. It
                can also use an index on those columns (with a backward
                scan) if
       <em class="replaceable">
        <code>
         key_part1
        </code>
       </em>
       is
                ascending and
       <em class="replaceable">
        <code>
         key_part2
        </code>
       </em>
       is
                descending. See
       <a class="xref" href="descending-indexes.html" title="10.3.13 Descending Indexes">
        Section 10.3.13, “Descending Indexes”
       </a>
       .
      </p>
     </li>
     <li class="listitem">
      <p>
       In the next two queries,
       <em class="replaceable">
        <code>
         key_part1
        </code>
       </em>
       is compared to a
                constant. The index is used if the
       <code class="literal">
        WHERE
       </code>
       clause is selective enough to
                make an index range scan cheaper than a table scan:
      </p>
      <div class="copytoclipboard-wrapper">
       <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa29667233"><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 keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> t1
  <span class="token keyword">WHERE</span> <em class="replaceable">key_part1</em> <span class="token operator">&gt;</span> <em class="replaceable">constant</em>
  <span class="token keyword">ORDER</span> <span class="token keyword">BY</span> <em class="replaceable">key_part1</em> <span class="token keyword">ASC</span><span class="token punctuation">;</span>

<span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> t1
  <span class="token keyword">WHERE</span> <em class="replaceable">key_part1</em> <span class="token operator">&lt;</span> <em class="replaceable">constant</em>
  <span class="token keyword">ORDER</span> <span class="token keyword">BY</span> <em class="replaceable">key_part1</em> <span class="token keyword">DESC</span><span class="token punctuation">;</span></code></pre>
      </div>
     </li>
     <li class="listitem">
      <p>
       In the next query, the
       <code class="literal">
        ORDER BY
       </code>
       does
                not name
       <em class="replaceable">
        <code>
         key_part1
        </code>
       </em>
       , but all
                rows selected have a constant
       <em class="replaceable">
        <code>
         key_part1
        </code>
       </em>
       value, so the index
                can still be used:
      </p>
      <div class="copytoclipboard-wrapper">
       <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa61870906"><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 keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> t1
  <span class="token keyword">WHERE</span> <em class="replaceable">key_part1</em> <span class="token operator">=</span> <em class="replaceable">constant1</em> <span class="token operator">AND</span> <em class="replaceable">key_part2</em> <span class="token operator">&gt;</span> <em class="replaceable">constant2</em>
  <span class="token keyword">ORDER</span> <span class="token keyword">BY</span> <em class="replaceable">key_part2</em><span class="token punctuation">;</span></code></pre>
      </div>
     </li>
    </ul>
   </div>
   <p>
    In some cases, MySQL
    <span class="emphasis">
     <em>
      cannot
     </em>
    </span>
    use indexes
            to resolve the
    <code class="literal">
     ORDER BY
    </code>
    , although it may
            still use indexes to find the rows that match the
    <code class="literal">
     WHERE
    </code>
    clause. Examples:
   </p>
   <div class="itemizedlist">
    <ul class="itemizedlist" style="list-style-type: disc; ">
     <li class="listitem">
      <p>
       The query uses
       <code class="literal">
        ORDER BY
       </code>
       on different
                indexes:
      </p>
      <div class="copytoclipboard-wrapper">
       <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa51198785"><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 keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> t1 <span class="token keyword">ORDER</span> <span class="token keyword">BY</span> <em class="replaceable">key1</em><span class="token punctuation">,</span> <em class="replaceable">key2</em><span class="token punctuation">;</span></code></pre>
      </div>
     </li>
     <li class="listitem">
      <p>
       The query uses
       <code class="literal">
        ORDER BY
       </code>
       on
                nonconsecutive parts of an index:
      </p>
      <div class="copytoclipboard-wrapper">
       <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa37305406"><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 keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> t1 <span class="token keyword">WHERE</span> <em class="replaceable">key2</em><span class="token operator">=</span><em class="replaceable">constant</em> <span class="token keyword">ORDER</span> <span class="token keyword">BY</span> <em class="replaceable">key1_part1</em><span class="token punctuation">,</span> <em class="replaceable">key1_part3</em><span class="token punctuation">;</span></code></pre>
      </div>
     </li>
     <li class="listitem">
      <p>
       The index used to fetch the rows differs from the one
                used in the
       <code class="literal">
        ORDER BY
       </code>
       :
      </p>
      <div class="copytoclipboard-wrapper">
       <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa19645393"><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 keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> t1 <span class="token keyword">WHERE</span> <em class="replaceable">key2</em><span class="token operator">=</span><em class="replaceable">constant</em> <span class="token keyword">ORDER</span> <span class="token keyword">BY</span> <em class="replaceable">key1</em><span class="token punctuation">;</span></code></pre>
      </div>
     </li>
     <li class="listitem">
      <p>
       The query uses
       <code class="literal">
        ORDER BY
       </code>
       with an
                expression that includes terms other than the index
                column name:
      </p>
      <div class="copytoclipboard-wrapper">
       <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa8220560"><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 keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> t1 <span class="token keyword">ORDER</span> <span class="token keyword">BY</span> <span class="token function">ABS</span><span class="token punctuation">(</span><span class="token keyword"><em class="replaceable">key</em></span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> t1 <span class="token keyword">ORDER</span> <span class="token keyword">BY</span> <span class="token operator">-</span><span class="token keyword"><em class="replaceable">key</em></span><span class="token punctuation">;</span></code></pre>
      </div>
     </li>
     <li class="listitem">
      <p>
       The query joins many tables, and the columns in the
       <code class="literal">
        ORDER BY
       </code>
       are not all from the first
                nonconstant table that is used to retrieve rows. (This
                is the first table in the
       <a class="link" href="explain.html" title="15.8.2 EXPLAIN Statement">
        <code class="literal">
         EXPLAIN
        </code>
       </a>
       output that does
                not have a
       <a class="link" href="explain-output.html#jointype_const">
        <code class="literal">
         const
        </code>
       </a>
       join
                type.)
      </p>
     </li>
     <li class="listitem">
      <p>
       The query has different
       <code class="literal">
        ORDER BY
       </code>
       and
       <code class="literal">
        GROUP BY
       </code>
       expressions.
      </p>
     </li>
     <li class="listitem">
      <p>
       There is an index on only a prefix of a column named in
                the
       <code class="literal">
        ORDER BY
       </code>
       clause. In this case,
                the index cannot be used to fully resolve the sort
                order. For example, if only the first 10 bytes of a
       <a class="link" href="char.html" title="13.3.2 The CHAR and VARCHAR Types">
        <code class="literal">
         CHAR(20)
        </code>
       </a>
       column are
                indexed, the index cannot distinguish values past the
                10th byte and a
       <code class="literal">
        filesort
       </code>
       is needed.
      </p>
     </li>
     <li class="listitem">
      <p>
       The index does not store rows in order. For example,
                this is true for a
       <code class="literal">
        HASH
       </code>
       index in a
       <code class="literal">
        MEMORY
       </code>
       table.
      </p>
     </li>
    </ul>
   </div>
   <p>
    Availability of an index for sorting may be affected by the
            use of column aliases. Suppose that the column
    <code class="literal">
     t1.a
    </code>
    is indexed. In this statement, the
            name of the column in the select list is
    <code class="literal">
     a
    </code>
    . It refers to
    <code class="literal">
     t1.a
    </code>
    ,
            as does the reference to
    <code class="literal">
     a
    </code>
    in the
    <code class="literal">
     ORDER BY
    </code>
    , so the index on
    <code class="literal">
     t1.a
    </code>
    can be used:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa92354081"><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 keyword">SELECT</span> a <span class="token keyword">FROM</span> t1 <span class="token keyword">ORDER</span> <span class="token keyword">BY</span> a<span class="token punctuation">;</span></code></pre>
   </div>
   <p>
    In this statement, the name of the column in the select list
            is also
    <code class="literal">
     a
    </code>
    , but it is the alias name. It
            refers to
    <code class="literal">
     ABS(a)
    </code>
    , as does the reference
            to
    <code class="literal">
     a
    </code>
    in the
    <code class="literal">
     ORDER BY
    </code>
    ,
            so the index on
    <code class="literal">
     t1.a
    </code>
    cannot be used:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa38911553"><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 keyword">SELECT</span> <span class="token function">ABS</span><span class="token punctuation">(</span>a<span class="token punctuation">)</span> <span class="token keyword">AS</span> a <span class="token keyword">FROM</span> t1 <span class="token keyword">ORDER</span> <span class="token keyword">BY</span> a<span class="token punctuation">;</span></code></pre>
   </div>
   <p>
    In the following statement, the
    <code class="literal">
     ORDER BY
    </code>
    refers to a name that is not the name of a column in the
            select list. But there is a column in
    <code class="literal">
     t1
    </code>
    named
    <code class="literal">
     a
    </code>
    , so the
    <code class="literal">
     ORDER
            BY
    </code>
    refers to
    <code class="literal">
     t1.a
    </code>
    and the index
            on
    <code class="literal">
     t1.a
    </code>
    can be used. (The resulting sort
            order may be completely different from the order for
    <code class="literal">
     ABS(a)
    </code>
    , of course.)
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa56657388"><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 keyword">SELECT</span> <span class="token function">ABS</span><span class="token punctuation">(</span>a<span class="token punctuation">)</span> <span class="token keyword">AS</span> b <span class="token keyword">FROM</span> t1 <span class="token keyword">ORDER</span> <span class="token keyword">BY</span> a<span class="token punctuation">;</span></code></pre>
   </div>
   <a class="indexterm" name="idm46045228872320">
   </a>
   <a class="indexterm" name="idm46045228870832">
   </a>
   <a class="indexterm" name="idm46045228869344">
   </a>
   <a class="indexterm" name="idm46045228867856">
   </a>
   <p>
    Previously (MySQL 8.3 and lower),
    <code class="literal">
     GROUP BY
    </code>
    sorted implicitly under certain
            conditions. In MySQL 8.4, that no longer
            occurs, so specifying
    <code class="literal">
     ORDER BY NULL
    </code>
    at
            the end to suppress implicit sorting (as was done
            previously) is no longer necessary. However, query results
            may differ from previous MySQL versions. To produce a given
            sort order, provide an
    <code class="literal">
     ORDER BY
    </code>
    clause.
   </p>
  </div>
  <div class="simplesect">
   <div class="titlepage">
    <div>
     <div class="simple">
      <h5 class="title">
       <a name="order-by-filesort">
       </a>
       Use of filesort to Satisfy ORDER BY
      </h5>
     </div>
    </div>
   </div>
   <a class="indexterm" name="idm46045228862528">
   </a>
   <a class="indexterm" name="idm46045228861488">
   </a>
   <p>
    If an index cannot be used to satisfy an
    <code class="literal">
     ORDER
            BY
    </code>
    clause, MySQL performs a
    <code class="literal">
     filesort
    </code>
    operation that reads table rows
            and sorts them. A
    <code class="literal">
     filesort
    </code>
    constitutes an
            extra sorting phase in query execution.
   </p>
   <p>
    To obtain memory for
    <code class="literal">
     filesort
    </code>
    operations,
            the optimizer allocates memory buffers incrementally as
            needed, up to the size indicated by the
    <a class="link" href="server-system-variables.html#sysvar_sort_buffer_size">
     <code class="literal">
      sort_buffer_size
     </code>
    </a>
    system
            variable. This enables users to set
    <a class="link" href="server-system-variables.html#sysvar_sort_buffer_size">
     <code class="literal">
      sort_buffer_size
     </code>
    </a>
    to larger
            values to speed up larger sorts, without concern for
            excessive memory use for small sorts. (This benefit may not
            occur for multiple concurrent sorts on Windows, which has a
            weak multithreaded
    <code class="literal">
     malloc
    </code>
    .)
   </p>
   <p>
    A
    <code class="literal">
     filesort
    </code>
    operation uses temporary disk
            files as necessary if the result set is too large to fit in
            memory. Some types of queries are particularly suited to
            completely in-memory
    <code class="literal">
     filesort
    </code>
    operations.
            For example, the optimizer can use
    <code class="literal">
     filesort
    </code>
    to efficiently handle in memory,
            without temporary files, the
    <code class="literal">
     ORDER BY
    </code>
    operation for queries (and subqueries) of the following
            form:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa13090108"><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 keyword">SELECT</span> <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span> <span class="token keyword">FROM</span> <em class="replaceable">single_table</em> <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span> <span class="token keyword">ORDER</span> <span class="token keyword">BY</span> <em class="replaceable">non_index_column</em> <span class="token punctuation">[</span><span class="token keyword">DESC</span><span class="token punctuation">]</span> <span class="token keyword">LIMIT</span> <span class="token punctuation">[</span><em class="replaceable">M</em><span class="token punctuation">,</span><span class="token punctuation">]</span><em class="replaceable">N</em><span class="token punctuation">;</span></code></pre>
   </div>
   <p>
    Such queries are common in web applications that display
            only a few rows from a larger result set. Examples:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa28370023"><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 keyword">SELECT</span> col1<span class="token punctuation">,</span> <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span> <span class="token keyword">FROM</span> t1 <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span> <span class="token keyword">ORDER</span> <span class="token keyword">BY</span> <span class="token keyword">name</span> <span class="token keyword">LIMIT</span> <span class="token number">10</span><span class="token punctuation">;</span>
<span class="token keyword">SELECT</span> col1<span class="token punctuation">,</span> <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span> <span class="token keyword">FROM</span> t1 <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span> <span class="token keyword">ORDER</span> <span class="token keyword">BY</span> <span class="token function">RAND</span><span class="token punctuation">(</span><span class="token punctuation">)</span> <span class="token keyword">LIMIT</span> <span class="token number">15</span><span class="token punctuation">;</span></code></pre>
   </div>
  </div>
  <div class="simplesect">
   <div class="titlepage">
    <div>
     <div class="simple">
      <h5 class="title">
       <a name="order-by-optimizer-control">
       </a>
       Influencing ORDER BY Optimization
      </h5>
     </div>
    </div>
   </div>
   <p>
    To increase
    <code class="literal">
     ORDER BY
    </code>
    speed, check whether
            you can get MySQL to use indexes rather than an extra
            sorting phase. If this is not possible, try the following
            strategies:
   </p>
   <div class="itemizedlist">
    <ul class="itemizedlist" style="list-style-type: disc; ">
     <li class="listitem">
      <p>
       Increase the
       <a class="link" href="server-system-variables.html#sysvar_sort_buffer_size">
        <code class="literal">
         sort_buffer_size
        </code>
       </a>
       variable value. Ideally, the value should be large
                enough for the entire result set to fit in the sort
                buffer (to avoid writes to disk and merge passes).
      </p>
      <p>
       Take into account that the size of column values stored
                in the sort buffer is affected by the
       <a class="link" href="server-system-variables.html#sysvar_max_sort_length">
        <code class="literal">
         max_sort_length
        </code>
       </a>
       system
                variable value. For example, if tuples store values of
                long string columns and you increase the value of
       <a class="link" href="server-system-variables.html#sysvar_max_sort_length">
        <code class="literal">
         max_sort_length
        </code>
       </a>
       , the
                size of sort buffer tuples increases as well and may
                require you to increase
       <a class="link" href="server-system-variables.html#sysvar_sort_buffer_size">
        <code class="literal">
         sort_buffer_size
        </code>
       </a>
       .
      </p>
      <p>
       To monitor the number of merge passes (to merge
                temporary files), check the
       <a class="link" href="server-status-variables.html#statvar_Sort_merge_passes">
        <code class="literal">
         Sort_merge_passes
        </code>
       </a>
       status variable.
      </p>
     </li>
     <li class="listitem">
      <p>
       Increase the
       <a class="link" href="server-system-variables.html#sysvar_read_rnd_buffer_size">
        <code class="literal">
         read_rnd_buffer_size
        </code>
       </a>
       variable value so that more rows are read at a time.
      </p>
     </li>
     <li class="listitem">
      <p>
       Change the
       <a class="link" href="server-system-variables.html#sysvar_tmpdir">
        <code class="literal">
         tmpdir
        </code>
       </a>
       system variable to point to a dedicated file system with
                large amounts of free space. The variable value can list
                several paths that are used in round-robin fashion; you
                can use this feature to spread the load across several
                directories. Separate the paths by colon characters
                (
       <code class="literal">
        :
       </code>
       ) on Unix and semicolon characters
                (
       <code class="literal">
        ;
       </code>
       ) on Windows. The paths should name
                directories in file systems located on different
       <span class="emphasis">
        <em>
         physical
        </em>
       </span>
       disks, not different
                partitions on the same disk.
      </p>
     </li>
    </ul>
   </div>
  </div>
  <div class="simplesect">
   <div class="titlepage">
    <div>
     <div class="simple">
      <h5 class="title">
       <a name="order-by-diagnostic-information">
       </a>
       ORDER BY Execution Plan Information Available
      </h5>
     </div>
    </div>
   </div>
   <p>
    With
    <a class="link" href="explain.html" title="15.8.2 EXPLAIN Statement">
     <code class="literal">
      EXPLAIN
     </code>
    </a>
    (see
    <a class="xref" href="using-explain.html" title="10.8.1 Optimizing Queries with EXPLAIN">
     Section 10.8.1, “Optimizing Queries with EXPLAIN”
    </a>
    ), you can check whether
            MySQL can use indexes to resolve an
    <code class="literal">
     ORDER
            BY
    </code>
    clause:
   </p>
   <div class="itemizedlist">
    <ul class="itemizedlist" style="list-style-type: disc; ">
     <li class="listitem">
      <p>
       If the
       <code class="literal">
        Extra
       </code>
       column of
       <a class="link" href="explain.html" title="15.8.2 EXPLAIN Statement">
        <code class="literal">
         EXPLAIN
        </code>
       </a>
       output does not
                contain
       <code class="literal">
        Using filesort
       </code>
       , the index is
                used and a
       <code class="literal">
        filesort
       </code>
       is not performed.
      </p>
     </li>
     <li class="listitem">
      <p>
       If the
       <code class="literal">
        Extra
       </code>
       column of
       <a class="link" href="explain.html" title="15.8.2 EXPLAIN Statement">
        <code class="literal">
         EXPLAIN
        </code>
       </a>
       output contains
       <code class="literal">
        Using filesort
       </code>
       , the index is not used
                and a
       <code class="literal">
        filesort
       </code>
       is performed.
      </p>
     </li>
    </ul>
   </div>
   <p>
    In addition, if a
    <code class="literal">
     filesort
    </code>
    is performed,
            optimizer trace output includes a
    <code class="literal">
     filesort_summary
    </code>
    block. For example:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-json"><div class="docs-select-all right" id="sa39076008"><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-json"><span class="token property">"filesort_summary"</span><span class="token operator">:</span> <span class="token punctuation">{</span>
  <span class="token property">"rows"</span><span class="token operator">:</span> <span class="token number">100</span><span class="token punctuation">,</span>
  <span class="token property">"examined_rows"</span><span class="token operator">:</span> <span class="token number">100</span><span class="token punctuation">,</span>
  <span class="token property">"number_of_tmp_files"</span><span class="token operator">:</span> <span class="token number">0</span><span class="token punctuation">,</span>
  <span class="token property">"peak_memory_used"</span><span class="token operator">:</span> <span class="token number">25192</span><span class="token punctuation">,</span>
  <span class="token property">"sort_mode"</span><span class="token operator">:</span> <span class="token string">"&lt;sort_key, packed_additional_fields&gt;"</span>
<span class="token punctuation">}</span></code></pre>
   </div>
   <p>
    <code class="literal">
     peak_memory_used
    </code>
    indicates the maximum
            memory used at any one time during the sort. This is a value
            up to but not necessarily as large as the value of the
    <a class="link" href="server-system-variables.html#sysvar_sort_buffer_size">
     <code class="literal">
      sort_buffer_size
     </code>
    </a>
    system
            variable. The optimizer allocates sort-buffer memory
            incrementally, beginning with a small amount and adding more
            as necessary, up to
    <code class="literal">
     sort_buffer_size
    </code>
    bytes.)
   </p>
   <p>
    The
    <code class="literal">
     sort_mode
    </code>
    value provides information
            about the contents of tuples in the sort buffer:
   </p>
   <div class="itemizedlist">
    <ul class="itemizedlist" style="list-style-type: disc; ">
     <li class="listitem">
      <p>
       <code class="literal">
        &lt;sort_key, rowid&gt;
       </code>
       : This
                indicates that sort buffer tuples are pairs that contain
                the sort key value and row ID of the original table row.
                Tuples are sorted by sort key value and the row ID is
                used to read the row from the table.
      </p>
     </li>
     <li class="listitem">
      <p>
       <code class="literal">
        &lt;sort_key, additional_fields&gt;
       </code>
       :
                This indicates that sort buffer tuples contain the sort
                key value and columns referenced by the query. Tuples
                are sorted by sort key value and column values are read
                directly from the tuple.
      </p>
     </li>
     <li class="listitem">
      <p>
       <code class="literal">
        &lt;sort_key,
                packed_additional_fields&gt;
       </code>
       : Like the
                previous variant, but the additional columns are packed
                tightly together instead of using a fixed-length
                encoding.
      </p>
     </li>
    </ul>
   </div>
   <p>
    <a class="link" href="explain.html" title="15.8.2 EXPLAIN Statement">
     <code class="literal">
      EXPLAIN
     </code>
    </a>
    does not distinguish
            whether the optimizer does or does not perform a
    <code class="literal">
     filesort
    </code>
    in memory. Use of an in-memory
    <code class="literal">
     filesort
    </code>
    can be seen in optimizer trace
            output. Look for
    <code class="literal">
     filesort_priority_queue_optimization
    </code>
    . For
            information about the optimizer trace, see
    <a class="xref" href="optimizer-tracing.html" title="10.15 Tracing the Optimizer">
     Section 10.15, “Tracing the Optimizer”
    </a>
    .
   </p>
  </div>
 </div>
 <br/>
</div>
