<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h3 class="title">
      <a name="index-hints">
      </a>
      10.9.4 Index Hints
     </h3>
    </div>
   </div>
  </div>
  <a class="indexterm" name="idm46045223458208">
  </a>
  <a class="indexterm" name="idm46045223457136">
  </a>
  <a class="indexterm" name="idm46045223455648">
  </a>
  <a class="indexterm" name="idm46045223454576">
  </a>
  <a class="indexterm" name="idm46045223453504">
  </a>
  <a class="indexterm" name="idm46045223452432">
  </a>
  <a class="indexterm" name="idm46045223451360">
  </a>
  <a class="indexterm" name="idm46045223450288">
  </a>
  <p>
   Index hints give the optimizer information about how to choose
        indexes during query processing. Index hints, described here,
        differ from optimizer hints, described in
   <a class="xref" href="optimizer-hints.html" title="10.9.3 Optimizer Hints">
    Section 10.9.3, “Optimizer Hints”
   </a>
   . Index and optimizer hints may
        be used separately or together.
  </p>
  <p>
   Index hints apply to
   <a class="link" href="select.html" title="15.2.13 SELECT Statement">
    <code class="literal">
     SELECT
    </code>
   </a>
   and
   <a class="link" href="update.html" title="15.2.17 UPDATE Statement">
    <code class="literal">
     UPDATE
    </code>
   </a>
   statements. They also work
        with multi-table
   <a class="link" href="delete.html" title="15.2.2 DELETE Statement">
    <code class="literal">
     DELETE
    </code>
   </a>
   statements, but not with single-table
   <code class="literal">
    DELETE
   </code>
   ,
        as shown later in this section.
  </p>
  <p>
   Index hints are specified following a table name. (For the
        general syntax for specifying tables in a
   <a class="link" href="select.html" title="15.2.13 SELECT Statement">
    <code class="literal">
     SELECT
    </code>
   </a>
   statement, see
   <a class="xref" href="join.html" title="15.2.13.2 JOIN Clause">
    Section 15.2.13.2, “JOIN Clause”
   </a>
   .) The syntax for referring to an
        individual table, including index hints, looks like this:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa15331343"><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"><em class="replaceable">tbl_name</em> <span class="token punctuation">[</span><span class="token punctuation">[</span><span class="token keyword">AS</span><span class="token punctuation">]</span> <em class="replaceable">alias</em><span class="token punctuation">]</span> <span class="token punctuation">[</span><em class="replaceable">index_hint_list</em><span class="token punctuation">]</span>

<em class="replaceable">index_hint_list</em>:
    <em class="replaceable">index_hint</em> <span class="token punctuation">[</span><em class="replaceable">index_hint</em><span class="token punctuation">]</span> <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span>

<em class="replaceable">index_hint</em>:
    <span class="token keyword">USE</span> {<span class="token keyword">INDEX</span><span class="token operator">|</span><span class="token keyword">KEY</span>}
      <span class="token punctuation">[</span><span class="token keyword">FOR</span> {<span class="token keyword">JOIN</span><span class="token operator">|</span><span class="token keyword">ORDER</span> <span class="token keyword">BY</span><span class="token operator">|</span><span class="token keyword">GROUP</span> <span class="token keyword">BY</span>}<span class="token punctuation">]</span> <span class="token punctuation">(</span><span class="token punctuation">[</span><em class="replaceable">index_list</em><span class="token punctuation">]</span><span class="token punctuation">)</span>
  <span class="token operator">|</span> {<span class="token keyword">IGNORE</span><span class="token operator">|</span><span class="token keyword">FORCE</span>} {<span class="token keyword">INDEX</span><span class="token operator">|</span><span class="token keyword">KEY</span>}
      <span class="token punctuation">[</span><span class="token keyword">FOR</span> {<span class="token keyword">JOIN</span><span class="token operator">|</span><span class="token keyword">ORDER</span> <span class="token keyword">BY</span><span class="token operator">|</span><span class="token keyword">GROUP</span> <span class="token keyword">BY</span>}<span class="token punctuation">]</span> <span class="token punctuation">(</span><em class="replaceable">index_list</em><span class="token punctuation">)</span>

<em class="replaceable">index_list</em>:
    <em class="replaceable">index_name</em> <span class="token punctuation">[</span><span class="token punctuation">,</span> <em class="replaceable">index_name</em><span class="token punctuation">]</span> <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span></code></pre>
  </div>
  <p>
   The
   <code class="literal">
    USE INDEX
        (
    <em class="replaceable">
     <code>
      index_list
     </code>
    </em>
    )
   </code>
   hint tells
        MySQL to use only one of the named indexes to find rows in the
        table. The alternative syntax
   <code class="literal">
    IGNORE INDEX
        (
    <em class="replaceable">
     <code>
      index_list
     </code>
    </em>
    )
   </code>
   tells MySQL to
        not use some particular index or indexes. These hints are useful
        if
   <a class="link" href="explain.html" title="15.8.2 EXPLAIN Statement">
    <code class="literal">
     EXPLAIN
    </code>
   </a>
   shows that MySQL is
        using the wrong index from the list of possible indexes.
  </p>
  <p>
   The
   <code class="literal">
    FORCE INDEX
   </code>
   hint acts like
   <code class="literal">
    USE
        INDEX (
    <em class="replaceable">
     <code>
      index_list
     </code>
    </em>
    )
   </code>
   , with
        the addition that a table scan is assumed to be
   <span class="emphasis">
    <em>
     very
    </em>
   </span>
   expensive. In other words, a table
        scan is used only if there is no way to use one of the named
        indexes to find rows in the table.
  </p>
  <div class="note" style="margin-left: 0.5in; margin-right: 0.5in;">
   <div class="admon-title">
    Note
   </div>
   <p>
    MySQL 8.4 supports the index-level optimizer
          hints
    <a class="link" href="optimizer-hints.html#optimizer-hints-index-level" title="Index-Level Optimizer Hints">
     <code class="literal">
      JOIN_INDEX
     </code>
    </a>
    ,
    <a class="link" href="optimizer-hints.html#optimizer-hints-index-level" title="Index-Level Optimizer Hints">
     <code class="literal">
      GROUP_INDEX
     </code>
    </a>
    ,
    <a class="link" href="optimizer-hints.html#optimizer-hints-index-level" title="Index-Level Optimizer Hints">
     <code class="literal">
      ORDER_INDEX
     </code>
    </a>
    , and
    <a class="link" href="optimizer-hints.html#optimizer-hints-index-level" title="Index-Level Optimizer Hints">
     <code class="literal">
      INDEX
     </code>
    </a>
    , which are equivalent
          to and intended to supersede
    <code class="literal">
     FORCE INDEX
    </code>
    index hints, as well as the
    <a class="link" href="optimizer-hints.html#optimizer-hints-index-level" title="Index-Level Optimizer Hints">
     <code class="literal">
      NO_JOIN_INDEX
     </code>
    </a>
    ,
    <a class="link" href="optimizer-hints.html#optimizer-hints-index-level" title="Index-Level Optimizer Hints">
     <code class="literal">
      NO_GROUP_INDEX
     </code>
    </a>
    ,
    <a class="link" href="optimizer-hints.html#optimizer-hints-index-level" title="Index-Level Optimizer Hints">
     <code class="literal">
      NO_ORDER_INDEX
     </code>
    </a>
    , and
    <a class="link" href="optimizer-hints.html#optimizer-hints-index-level" title="Index-Level Optimizer Hints">
     <code class="literal">
      NO_INDEX
     </code>
    </a>
    optimizer hints,
          which are equivalent to and intended to supersede
    <code class="literal">
     IGNORE INDEX
    </code>
    index hints. Thus, you should
          expect
    <code class="literal">
     USE INDEX
    </code>
    ,
    <code class="literal">
     FORCE
          INDEX
    </code>
    , and
    <code class="literal">
     IGNORE INDEX
    </code>
    to be
          deprecated in a future release of MySQL, and at some time
          thereafter to be removed altogether.
   </p>
   <p>
    These index-level optimizer hints are supported with both
          single-table and multi-table
    <a class="link" href="delete.html" title="15.2.2 DELETE Statement">
     <code class="literal">
      DELETE
     </code>
    </a>
    statements.
   </p>
   <p>
    For more information, see
    <a class="xref" href="optimizer-hints.html#optimizer-hints-index-level" title="Index-Level Optimizer Hints">
     Index-Level Optimizer Hints
    </a>
    .
   </p>
  </div>
  <p>
   Each hint requires index names, not column names. To refer to a
        primary key, use the name
   <code class="literal">
    PRIMARY
   </code>
   . To see the
        index names for a table, use the
   <a class="link" href="show-index.html" title="15.7.7.23 SHOW INDEX Statement">
    <code class="literal">
     SHOW
        INDEX
    </code>
   </a>
   statement or the Information Schema
   <a class="link" href="information-schema-statistics-table.html" title="28.3.34 The INFORMATION_SCHEMA STATISTICS Table">
    <code class="literal">
     STATISTICS
    </code>
   </a>
   table.
  </p>
  <p>
   An
   <em class="replaceable">
    <code>
     index_name
    </code>
   </em>
   value need not be a
        full index name. It can be an unambiguous prefix of an index
        name. If a prefix is ambiguous, an error occurs.
  </p>
  <p>
   Examples:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa69411628"><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> table1 <span class="token keyword">USE</span> <span class="token keyword">INDEX</span> <span class="token punctuation">(</span>col1_index<span class="token punctuation">,</span>col2_index<span class="token punctuation">)</span>
  <span class="token keyword">WHERE</span> col1<span class="token operator">=</span><span class="token number">1</span> <span class="token operator">AND</span> col2<span class="token operator">=</span><span class="token number">2</span> <span class="token operator">AND</span> col3<span class="token operator">=</span><span class="token number">3</span><span class="token punctuation">;</span>

<span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> table1 <span class="token keyword">IGNORE</span> <span class="token keyword">INDEX</span> <span class="token punctuation">(</span>col3_index<span class="token punctuation">)</span>
  <span class="token keyword">WHERE</span> col1<span class="token operator">=</span><span class="token number">1</span> <span class="token operator">AND</span> col2<span class="token operator">=</span><span class="token number">2</span> <span class="token operator">AND</span> col3<span class="token operator">=</span><span class="token number">3</span><span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   The syntax for index hints has the following characteristics:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      It is syntactically valid to omit
      <em class="replaceable">
       <code>
        index_list
       </code>
      </em>
      for
      <code class="literal">
       USE
            INDEX
      </code>
      , which means
      <span class="quote">
       “
       <span class="quote">
        use no indexes.
       </span>
       ”
      </span>
      Omitting
      <em class="replaceable">
       <code>
        index_list
       </code>
      </em>
      for
      <code class="literal">
       FORCE INDEX
      </code>
      or
      <code class="literal">
       IGNORE
            INDEX
      </code>
      is a syntax error.
     </p>
    </li>
    <li class="listitem">
     <p>
      You can specify the scope of an index hint by adding a
      <code class="literal">
       FOR
      </code>
      clause to the hint. This provides
            more fine-grained control over optimizer selection of an
            execution plan for various phases of query processing. To
            affect only the indexes used when MySQL decides how to find
            rows in the table and how to process joins, use
      <code class="literal">
       FOR
            JOIN
      </code>
      . To influence index usage for sorting or
            grouping rows, use
      <code class="literal">
       FOR ORDER BY
      </code>
      or
      <code class="literal">
       FOR GROUP BY
      </code>
      .
     </p>
    </li>
    <li class="listitem">
     <p>
      You can specify multiple index hints:
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa19663640"><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">USE</span> <span class="token keyword">INDEX</span> <span class="token punctuation">(</span>i1<span class="token punctuation">)</span> <span class="token keyword">IGNORE</span> <span class="token keyword">INDEX</span> <span class="token keyword">FOR</span> <span class="token keyword">ORDER</span> <span class="token keyword">BY</span> <span class="token punctuation">(</span>i2<span class="token punctuation">)</span> <span class="token keyword">ORDER</span> <span class="token keyword">BY</span> a<span class="token punctuation">;</span></code></pre>
     </div>
     <p>
      It is not an error to name the same index in several hints
            (even within the same hint):
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa91448944"><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">USE</span> <span class="token keyword">INDEX</span> <span class="token punctuation">(</span>i1<span class="token punctuation">)</span> <span class="token keyword">USE</span> <span class="token keyword">INDEX</span> <span class="token punctuation">(</span>i1<span class="token punctuation">,</span>i1<span class="token punctuation">)</span><span class="token punctuation">;</span></code></pre>
     </div>
     <p>
      However, it is an error to mix
      <code class="literal">
       USE INDEX
      </code>
      and
      <code class="literal">
       FORCE INDEX
      </code>
      for the same table:
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa43267009"><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">USE</span> <span class="token keyword">INDEX</span> <span class="token keyword">FOR</span> <span class="token keyword">JOIN</span> <span class="token punctuation">(</span>i1<span class="token punctuation">)</span> <span class="token keyword">FORCE</span> <span class="token keyword">INDEX</span> <span class="token keyword">FOR</span> <span class="token keyword">JOIN</span> <span class="token punctuation">(</span>i2<span class="token punctuation">)</span><span class="token punctuation">;</span></code></pre>
     </div>
    </li>
   </ul>
  </div>
  <p>
   If an index hint includes no
   <code class="literal">
    FOR
   </code>
   clause, the
        scope of the hint is to apply to all parts of the statement. For
        example, this hint:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa22676585"><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">IGNORE</span> <span class="token keyword">INDEX</span> <span class="token punctuation">(</span>i1<span class="token punctuation">)</span></code></pre>
  </div>
  <p>
   is equivalent to this combination of hints:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa33641077"><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">IGNORE</span> <span class="token keyword">INDEX</span> <span class="token keyword">FOR</span> <span class="token keyword">JOIN</span> <span class="token punctuation">(</span>i1<span class="token punctuation">)</span>
<span class="token keyword">IGNORE</span> <span class="token keyword">INDEX</span> <span class="token keyword">FOR</span> <span class="token keyword">ORDER</span> <span class="token keyword">BY</span> <span class="token punctuation">(</span>i1<span class="token punctuation">)</span>
<span class="token keyword">IGNORE</span> <span class="token keyword">INDEX</span> <span class="token keyword">FOR</span> <span class="token keyword">GROUP</span> <span class="token keyword">BY</span> <span class="token punctuation">(</span>i1<span class="token punctuation">)</span></code></pre>
  </div>
  <p>
   When index hints are processed, they are collected in a single
        list by type (
   <code class="literal">
    USE
   </code>
   ,
   <code class="literal">
    FORCE
   </code>
   ,
   <code class="literal">
    IGNORE
   </code>
   ) and by scope (
   <code class="literal">
    FOR
        JOIN
   </code>
   ,
   <code class="literal">
    FOR ORDER BY
   </code>
   ,
   <code class="literal">
    FOR
        GROUP BY
   </code>
   ). For example:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa42908943"><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">USE</span> <span class="token keyword">INDEX</span> <span class="token punctuation">(</span><span class="token punctuation">)</span> <span class="token keyword">IGNORE</span> <span class="token keyword">INDEX</span> <span class="token punctuation">(</span>i2<span class="token punctuation">)</span> <span class="token keyword">USE</span> <span class="token keyword">INDEX</span> <span class="token punctuation">(</span>i1<span class="token punctuation">)</span> <span class="token keyword">USE</span> <span class="token keyword">INDEX</span> <span class="token punctuation">(</span>i2<span class="token punctuation">)</span><span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   is equivalent to:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa48495606"><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">USE</span> <span class="token keyword">INDEX</span> <span class="token punctuation">(</span>i1<span class="token punctuation">,</span>i2<span class="token punctuation">)</span> <span class="token keyword">IGNORE</span> <span class="token keyword">INDEX</span> <span class="token punctuation">(</span>i2<span class="token punctuation">)</span><span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   The index hints then are applied for each scope in the following
        order:
  </p>
  <div class="orderedlist">
   <ol class="orderedlist" type="1">
    <li class="listitem">
     <p>
      <code class="literal">
       {USE|FORCE} INDEX
      </code>
      is applied if present.
            (If not, the optimizer-determined set of indexes is used.)
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       IGNORE INDEX
      </code>
      is applied over the result
            of the previous step. For example, the following two queries
            are equivalent:
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa88656092"><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">USE</span> <span class="token keyword">INDEX</span> <span class="token punctuation">(</span>i1<span class="token punctuation">)</span> <span class="token keyword">IGNORE</span> <span class="token keyword">INDEX</span> <span class="token punctuation">(</span>i2<span class="token punctuation">)</span> <span class="token keyword">USE</span> <span class="token keyword">INDEX</span> <span class="token punctuation">(</span>i2<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">USE</span> <span class="token keyword">INDEX</span> <span class="token punctuation">(</span>i1<span class="token punctuation">)</span><span class="token punctuation">;</span></code></pre>
     </div>
    </li>
   </ol>
  </div>
  <p>
   For
   <code class="literal">
    FULLTEXT
   </code>
   searches, index hints work as
        follows:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      For natural language mode searches, index hints are silently
            ignored. For example,
      <code class="literal">
       IGNORE INDEX(i1)
      </code>
      is
            ignored with no warning and the index is still used.
     </p>
    </li>
    <li class="listitem">
     <p>
      For boolean mode searches, index hints with
      <code class="literal">
       FOR
            ORDER BY
      </code>
      or
      <code class="literal">
       FOR GROUP BY
      </code>
      are
            silently ignored. Index hints with
      <code class="literal">
       FOR
            JOIN
      </code>
      or no
      <code class="literal">
       FOR
      </code>
      modifier are
            honored. In contrast to how hints apply for
            non-
      <code class="literal">
       FULLTEXT
      </code>
      searches, the hint is used
            for all phases of query execution (finding rows and
            retrieval, grouping, and ordering). This is true even if the
            hint is given for a non-
      <code class="literal">
       FULLTEXT
      </code>
      index.
     </p>
     <p>
      For example, the following two queries are equivalent:
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa57663633"><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> t
  <span class="token keyword">USE</span> <span class="token keyword">INDEX</span> <span class="token punctuation">(</span>index1<span class="token punctuation">)</span>
  <span class="token keyword">IGNORE</span> <span class="token keyword">INDEX</span> <span class="token keyword">FOR</span> <span class="token keyword">ORDER</span> <span class="token keyword">BY</span> <span class="token punctuation">(</span>index1<span class="token punctuation">)</span>
  <span class="token keyword">IGNORE</span> <span class="token keyword">INDEX</span> <span class="token keyword">FOR</span> <span class="token keyword">GROUP</span> <span class="token keyword">BY</span> <span class="token punctuation">(</span>index1<span class="token punctuation">)</span>
  <span class="token keyword">WHERE</span> <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span> <span class="token keyword">IN</span> <span class="token datatype">BOOLEAN</span> <span class="token keyword">MODE</span> <span class="token punctuation">.</span><span class="token punctuation">.</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> t
  <span class="token keyword">USE</span> <span class="token keyword">INDEX</span> <span class="token punctuation">(</span>index1<span class="token punctuation">)</span>
  <span class="token keyword">WHERE</span> <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span> <span class="token keyword">IN</span> <span class="token datatype">BOOLEAN</span> <span class="token keyword">MODE</span> <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span> <span class="token punctuation">;</span></code></pre>
     </div>
    </li>
   </ul>
  </div>
  <p>
   Index hints work with
   <a class="link" href="delete.html" title="15.2.2 DELETE Statement">
    <code class="literal">
     DELETE
    </code>
   </a>
   statements, but only if you use multi-table
   <code class="literal">
    DELETE
   </code>
   syntax, as shown here:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa63136695"><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">DELETE</span> <span class="token keyword">FROM</span> t1 <span class="token keyword">USE</span> <span class="token keyword">INDEX</span><span class="token punctuation">(</span>col2<span class="token punctuation">)</span> 
<span class="token prompt">    -&gt;</span> <span class="token keyword">WHERE</span> col1 <span class="token operator">BETWEEN</span> <span class="token number">1</span> <span class="token operator">AND</span> <span class="token number">100</span> <span class="token operator">AND</span> COL2 <span class="token operator">BETWEEN</span> <span class="token number">1</span> <span class="token operator">AND</span> <span class="token number">100</span>\G
<span class="token output">ERROR 1064 (42000)<span class="token punctuation">:</span> You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near 'use
index(col2) where col1 between 1 and 100 and col2 between 1 and 100' at line 1
  </span>
<span class="token prompt">mysql&gt;</span> <span class="token keyword">EXPLAIN</span> <span class="token keyword">DELETE</span> t1<span class="token punctuation">.</span><span class="token operator">*</span> <span class="token keyword">FROM</span> t1 <span class="token keyword">USE</span> <span class="token keyword">INDEX</span><span class="token punctuation">(</span>col2<span class="token punctuation">)</span> 
<span class="token prompt">    -&gt;</span> <span class="token keyword">WHERE</span> col1 <span class="token operator">BETWEEN</span> <span class="token number">1</span> <span class="token operator">AND</span> <span class="token number">100</span> <span class="token operator">AND</span> COL2 <span class="token operator">BETWEEN</span> <span class="token number">1</span> <span class="token operator">AND</span> <span class="token number">100</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> DELETE
        table<span class="token punctuation">:</span> t1
   partitions<span class="token punctuation">:</span> NULL
         type<span class="token punctuation">:</span> range
possible_keys<span class="token punctuation">:</span> col2
          key<span class="token punctuation">:</span> col2
      key_len<span class="token punctuation">:</span> 5
          ref<span class="token punctuation">:</span> NULL
         rows<span class="token punctuation">:</span> 72
     filtered<span class="token punctuation">:</span> 11.11
        Extra<span class="token punctuation">:</span> Using where
</span><span class="token output">1 row in set, 1 warning (0.00 sec)</span></code></pre>
  </div>
 </div>
 <br/>
</div>
