<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h3 class="title">
      <a name="descending-indexes">
      </a>
      10.3.13 Descending Indexes
     </h3>
    </div>
   </div>
  </div>
  <a class="indexterm" name="idm46045226971456">
  </a>
  <a class="indexterm" name="idm46045226970384">
  </a>
  <p>
   MySQL supports descending indexes:
   <code class="literal">
    DESC
   </code>
   in an
        index definition is no longer ignored but causes storage of key
        values in descending order. Previously, indexes could be scanned
        in reverse order but at a performance penalty. A descending
        index can be scanned in forward order, which is more efficient.
        Descending indexes also make it possible for the optimizer to
        use multiple-column indexes when the most efficient scan order
        mixes ascending order for some columns and descending order for
        others.
  </p>
  <p>
   Consider the following table definition, which contains two
        columns and four two-column index definitions for the various
        combinations of ascending and descending indexes on the columns:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa17229852"><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">CREATE</span> <span class="token keyword">TABLE</span> t <span class="token punctuation">(</span>
  c1 <span class="token datatype">INT</span><span class="token punctuation">,</span> c2 <span class="token datatype">INT</span><span class="token punctuation">,</span>
  <span class="token keyword">INDEX</span> idx1 <span class="token punctuation">(</span>c1 <span class="token keyword">ASC</span><span class="token punctuation">,</span> c2 <span class="token keyword">ASC</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
  <span class="token keyword">INDEX</span> idx2 <span class="token punctuation">(</span>c1 <span class="token keyword">ASC</span><span class="token punctuation">,</span> c2 <span class="token keyword">DESC</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
  <span class="token keyword">INDEX</span> idx3 <span class="token punctuation">(</span>c1 <span class="token keyword">DESC</span><span class="token punctuation">,</span> c2 <span class="token keyword">ASC</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
  <span class="token keyword">INDEX</span> idx4 <span class="token punctuation">(</span>c1 <span class="token keyword">DESC</span><span class="token punctuation">,</span> c2 <span class="token keyword">DESC</span><span class="token punctuation">)</span>
<span class="token punctuation">)</span><span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   The table definition results in four distinct indexes. The
        optimizer can perform a forward index scan for each of the
   <code class="literal">
    ORDER BY
   </code>
   clauses and need not use a
   <code class="literal">
    filesort
   </code>
   operation:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa25190182"><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">ORDER</span> <span class="token keyword">BY</span> c1 <span class="token keyword">ASC</span><span class="token punctuation">,</span> c2 <span class="token keyword">ASC</span>    <span class="token comment" spellcheck="true">-- optimizer can use idx1</span>
<span class="token keyword">ORDER</span> <span class="token keyword">BY</span> c1 <span class="token keyword">DESC</span><span class="token punctuation">,</span> c2 <span class="token keyword">DESC</span>  <span class="token comment" spellcheck="true">-- optimizer can use idx4</span>
<span class="token keyword">ORDER</span> <span class="token keyword">BY</span> c1 <span class="token keyword">ASC</span><span class="token punctuation">,</span> c2 <span class="token keyword">DESC</span>   <span class="token comment" spellcheck="true">-- optimizer can use idx2</span>
<span class="token keyword">ORDER</span> <span class="token keyword">BY</span> c1 <span class="token keyword">DESC</span><span class="token punctuation">,</span> c2 <span class="token keyword">ASC</span>   <span class="token comment" spellcheck="true">-- optimizer can use idx3</span></code></pre>
  </div>
  <p>
   Use of descending indexes is subject to these conditions:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      Descending indexes are supported only for the
      <code class="literal">
       InnoDB
      </code>
      storage engine, with these
            limitations:
     </p>
     <div class="itemizedlist">
      <ul class="itemizedlist" style="list-style-type: circle; ">
       <li class="listitem">
        <p>
         Change buffering is not supported for a secondary index
                if the index contains a descending index key column or
                if the primary key includes a descending index column.
        </p>
       </li>
       <li class="listitem">
        <p>
         The
         <code class="literal">
          InnoDB
         </code>
         SQL parser does not use
                descending indexes. For
         <code class="literal">
          InnoDB
         </code>
         full-text search, this means that the index required on
                the
         <code class="literal">
          FTS_DOC_ID
         </code>
         column of the indexed
                table cannot be defined as a descending index. For more
                information, see
         <a class="xref" href="innodb-fulltext-index.html" title="17.6.2.4 InnoDB Full-Text Indexes">
          Section 17.6.2.4, “InnoDB Full-Text Indexes”
         </a>
         .
        </p>
       </li>
      </ul>
     </div>
    </li>
    <li class="listitem">
     <p>
      Descending indexes are supported for all data types for
            which ascending indexes are available.
     </p>
    </li>
    <li class="listitem">
     <p>
      Descending indexes are supported for ordinary (nongenerated)
            and generated columns (both
      <code class="literal">
       VIRTUAL
      </code>
      and
      <code class="literal">
       STORED
      </code>
      ).
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       DISTINCT
      </code>
      can use any index containing
            matching columns, including descending key parts.
     </p>
    </li>
    <li class="listitem">
     <p>
      Indexes that have descending key parts are not used for
      <a class="link" href="aggregate-functions.html#function_min">
       <code class="literal">
        MIN()
       </code>
      </a>
      /
      <a class="link" href="aggregate-functions.html#function_max">
       <code class="literal">
        MAX()
       </code>
      </a>
      optimization of queries that invoke aggregate functions but
            do not have a
      <code class="literal">
       GROUP BY
      </code>
      clause.
     </p>
    </li>
    <li class="listitem">
     <p>
      Descending indexes are supported for
      <code class="literal">
       BTREE
      </code>
      but not
      <code class="literal">
       HASH
      </code>
      indexes. Descending indexes are not supported for
      <code class="literal">
       FULLTEXT
      </code>
      or
      <code class="literal">
       SPATIAL
      </code>
      indexes.
     </p>
     <p>
      Explicitly specified
      <code class="literal">
       ASC
      </code>
      and
      <code class="literal">
       DESC
      </code>
      designators for
      <code class="literal">
       HASH
      </code>
      ,
      <code class="literal">
       FULLTEXT
      </code>
      , and
      <code class="literal">
       SPATIAL
      </code>
      indexes results in an error.
     </p>
    </li>
   </ul>
  </div>
  <p>
   You can see in the
   <strong class="userinput">
    <code>
     Extra
    </code>
   </strong>
   column of the
        output of
   <a class="link" href="explain.html" title="15.8.2 EXPLAIN Statement">
    <code class="literal">
     EXPLAIN
    </code>
   </a>
   that the
        optimizer is able to use a descending index, as shown here:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa58505571"><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">CREATE</span> <span class="token keyword">TABLE</span> t1 <span class="token punctuation">(</span>
<span class="token prompt">    -&gt;</span> a <span class="token datatype">INT</span><span class="token punctuation">,</span> 
<span class="token prompt">    -&gt;</span> b <span class="token datatype">INT</span><span class="token punctuation">,</span> 
<span class="token prompt">    -&gt;</span> <span class="token keyword">INDEX</span> a_desc_b_asc <span class="token punctuation">(</span>a <span class="token keyword">DESC</span><span class="token punctuation">,</span> b <span class="token keyword">ASC</span><span class="token punctuation">)</span>
<span class="token prompt">    -&gt;</span> <span class="token punctuation">)</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> <span class="token operator">*</span> <span class="token keyword">FROM</span> t1 <span class="token keyword">ORDER</span> <span class="token keyword">BY</span> a <span class="token keyword">ASC</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> t1
   partitions<span class="token punctuation">:</span> NULL
         type<span class="token punctuation">:</span> index
possible_keys<span class="token punctuation">:</span> NULL
          key<span class="token punctuation">:</span> a_desc_b_asc
      key_len<span class="token punctuation">:</span> 10
          ref<span class="token punctuation">:</span> NULL
         rows<span class="token punctuation">:</span> 1
     filtered<span class="token punctuation">:</span> 100.00
        Extra<span class="token punctuation">:</span> Backward index scan; Using index</span></code></pre>
  </div>
  <p>
   In
   <code class="literal">
    EXPLAIN FORMAT=TREE
   </code>
   output, use of a
        descending index is indicated by the addition of
   <code class="literal">
    (reverse)
   </code>
   following the name of the index,
        like this:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa96342315"><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">FORMAT</span><span class="token operator">=</span><span class="token keyword">TREE</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> a <span class="token keyword">ASC</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>
EXPLAIN<span class="token punctuation">:</span> -&gt; Index scan on t1 using a_desc_b_asc (reverse)  (cost=0.35 rows=1)</span></code></pre>
  </div>
  <p>
   See also
   <a class="xref" href="explain-output.html#explain-extra-information" title="EXPLAIN Extra Information">
    EXPLAIN Extra Information
   </a>
   .
  </p>
 </div>
 <br/>
</div>
