<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h3 class="title">
      <a name="invisible-indexes">
      </a>
      10.3.12 Invisible Indexes
     </h3>
    </div>
   </div>
  </div>
  <a class="indexterm" name="idm46045227027712">
  </a>
  <p>
   MySQL supports invisible indexes; that is, indexes that are not
        used by the optimizer. The feature applies to indexes other than
        primary keys (either explicit or implicit).
  </p>
  <p>
   Indexes are visible by default. To control visibility explicitly
        for a new index, use a
   <code class="literal">
    VISIBLE
   </code>
   or
   <code class="literal">
    INVISIBLE
   </code>
   keyword as part of the index
        definition for
   <a class="link" href="create-table.html" title="15.1.20 CREATE TABLE Statement">
    <code class="literal">
     CREATE TABLE
    </code>
   </a>
   ,
   <a class="link" href="create-index.html" title="15.1.15 CREATE INDEX Statement">
    <code class="literal">
     CREATE INDEX
    </code>
   </a>
   , or
   <a class="link" href="alter-table.html" title="15.1.9 ALTER TABLE Statement">
    <code class="literal">
     ALTER TABLE
    </code>
   </a>
   :
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa55693823"><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> t1 <span class="token punctuation">(</span>
  i <span class="token datatype">INT</span><span class="token punctuation">,</span>
  j <span class="token datatype">INT</span><span class="token punctuation">,</span>
  k <span class="token datatype">INT</span><span class="token punctuation">,</span>
  <span class="token keyword">INDEX</span> i_idx <span class="token punctuation">(</span>i<span class="token punctuation">)</span> <span class="token keyword">INVISIBLE</span>
<span class="token punctuation">)</span> <span class="token keyword">ENGINE</span> <span class="token operator">=</span> InnoDB<span class="token punctuation">;</span>
<span class="token keyword">CREATE</span> <span class="token keyword">INDEX</span> j_idx <span class="token keyword">ON</span> t1 <span class="token punctuation">(</span>j<span class="token punctuation">)</span> <span class="token keyword">INVISIBLE</span><span class="token punctuation">;</span>
<span class="token keyword">ALTER</span> <span class="token keyword">TABLE</span> t1 <span class="token keyword">ADD</span> <span class="token keyword">INDEX</span> k_idx <span class="token punctuation">(</span>k<span class="token punctuation">)</span> <span class="token keyword">INVISIBLE</span><span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   To alter the visibility of an existing index, use a
   <code class="literal">
    VISIBLE
   </code>
   or
   <code class="literal">
    INVISIBLE
   </code>
   keyword with the
   <code class="literal">
    ALTER TABLE ... ALTER INDEX
   </code>
   operation:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa70964676"><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">ALTER</span> <span class="token keyword">TABLE</span> t1 <span class="token keyword">ALTER</span> <span class="token keyword">INDEX</span> i_idx <span class="token keyword">INVISIBLE</span><span class="token punctuation">;</span>
<span class="token keyword">ALTER</span> <span class="token keyword">TABLE</span> t1 <span class="token keyword">ALTER</span> <span class="token keyword">INDEX</span> i_idx <span class="token keyword">VISIBLE</span><span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   Information about whether an index is visible or invisible is
        available from 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 or
   <a class="link" href="show-index.html" title="15.7.7.23 SHOW INDEX Statement">
    <code class="literal">
     SHOW INDEX
    </code>
   </a>
   output. For example:
  </p>
  <a class="indexterm" name="idm46045227012560">
  </a>
  <a class="indexterm" name="idm46045227011072">
  </a>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa44552294"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token prompt">mysql&gt;</span> <span class="token keyword">SELECT</span> INDEX_NAME<span class="token punctuation">,</span> IS_VISIBLE
       <span class="token keyword">FROM</span> INFORMATION_SCHEMA<span class="token punctuation">.</span>STATISTICS
       <span class="token keyword">WHERE</span> TABLE_SCHEMA <span class="token operator">=</span> <span class="token string">'db1'</span> <span class="token operator">AND</span> <span class="token keyword">TABLE_NAME</span> <span class="token operator">=</span> <span class="token string">'t1'</span><span class="token punctuation">;</span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span> INDEX_NAME <span class="token punctuation">|</span> IS_VISIBLE <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span> i_idx      <span class="token punctuation">|</span> YES        <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> j_idx      <span class="token punctuation">|</span> NO         <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> k_idx      <span class="token punctuation">|</span> NO         <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span></code></pre>
  </div>
  <p>
   Invisible indexes make it possible to test the effect of
        removing an index on query performance, without making a
        destructive change that must be undone should the index turn out
        to be required. Dropping and re-adding an index can be expensive
        for a large table, whereas making it invisible and visible are
        fast, in-place operations.
  </p>
  <p>
   If an index made invisible actually is needed or used by the
        optimizer, there are several ways to notice the effect of its
        absence on queries for the table:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      Errors occur for queries that include index hints that refer
            to the invisible index.
     </p>
    </li>
    <li class="listitem">
     <p>
      Performance Schema data shows an increase in workload for
            affected queries.
     </p>
    </li>
    <li class="listitem">
     <p>
      Queries have different
      <a class="link" href="explain.html" title="15.8.2 EXPLAIN Statement">
       <code class="literal">
        EXPLAIN
       </code>
      </a>
      execution plans.
     </p>
    </li>
    <li class="listitem">
     <p>
      Queries appear in the slow query log that did not appear
            there previously.
     </p>
    </li>
   </ul>
  </div>
  <a class="indexterm" name="idm46045226999776">
  </a>
  <a class="indexterm" name="idm46045226998256">
  </a>
  <p>
   The
   <a class="link" href="switchable-optimizations.html#optflag_use-invisible-indexes">
    <code class="literal">
     use_invisible_indexes
    </code>
   </a>
   flag
        of the
   <a class="link" href="server-system-variables.html#sysvar_optimizer_switch">
    <code class="literal">
     optimizer_switch
    </code>
   </a>
   system
        variable controls whether the optimizer uses invisible indexes
        for query execution plan construction. If the flag is
   <code class="literal">
    off
   </code>
   (the default), the optimizer ignores
        invisible indexes (the same behavior as prior to the
        introduction of this flag). If the flag is
   <code class="literal">
    on
   </code>
   , invisible indexes remain invisible but
        the optimizer takes them into account for execution plan
        construction.
  </p>
  <p>
   Using the
   <a class="link" href="optimizer-hints.html#optimizer-hints-set-var" title="Variable-Setting Hint Syntax">
    <code class="literal">
     SET_VAR
    </code>
   </a>
   optimizer
        hint to update the value of
   <a class="link" href="server-system-variables.html#sysvar_optimizer_switch">
    <code class="literal">
     optimizer_switch
    </code>
   </a>
   temporarily,
        you can enable invisible indexes for the duration of a single
        query only, like this:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa16262817"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token prompt">mysql&gt;</span> <span class="token keyword">EXPLAIN</span> <span class="token keyword">SELECT</span> <span class="token comment" spellcheck="true">/*+ SET_VAR(optimizer_switch = 'use_invisible_indexes=on') */</span>
     <span class="token operator">&gt;</span>     i<span class="token punctuation">,</span> j <span class="token keyword">FROM</span> t1 <span class="token keyword">WHERE</span> j <span class="token operator">&gt;=</span> <span class="token number">50</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> range
possible_keys<span class="token punctuation">:</span> j_idx
          key<span class="token punctuation">:</span> j_idx
      key_len<span class="token punctuation">:</span> 5
          ref<span class="token punctuation">:</span> NULL
         rows<span class="token punctuation">:</span> 2
     filtered<span class="token punctuation">:</span> 100.00
        Extra<span class="token punctuation">:</span> Using index condition
</span>
<span class="token prompt">mysql&gt;</span> <span class="token keyword">EXPLAIN</span> <span class="token keyword">SELECT</span> i<span class="token punctuation">,</span> j <span class="token keyword">FROM</span> t1 <span class="token keyword">WHERE</span> j <span class="token operator">&gt;=</span> <span class="token number">50</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> ALL
possible_keys<span class="token punctuation">:</span> NULL
          key<span class="token punctuation">:</span> NULL
      key_len<span class="token punctuation">:</span> NULL
          ref<span class="token punctuation">:</span> NULL
         rows<span class="token punctuation">:</span> 5
     filtered<span class="token punctuation">:</span> 33.33
        Extra<span class="token punctuation">:</span> Using where</span></code></pre>
  </div>
  <p>
   Index visibility does not affect index maintenance. For example,
        an index continues to be updated per changes to table rows, and
        a unique index prevents insertion of duplicates into a column,
        regardless of whether the index is visible or invisible.
  </p>
  <p>
   A table with no explicit primary key may still have an effective
        implicit primary key if it has any
   <code class="literal">
    UNIQUE
   </code>
   indexes on
   <code class="literal">
    NOT NULL
   </code>
   columns. In this case,
        the first such index places the same constraint on table rows as
        an explicit primary key and that index cannot be made invisible.
        Consider the following table definition:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa5318849"><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> t2 <span class="token punctuation">(</span>
  i <span class="token datatype">INT</span> <span class="token operator">NOT</span> <span class="token boolean">NULL</span><span class="token punctuation">,</span>
  j <span class="token datatype">INT</span> <span class="token operator">NOT</span> <span class="token boolean">NULL</span><span class="token punctuation">,</span>
  <span class="token keyword">UNIQUE</span> j_idx <span class="token punctuation">(</span>j<span class="token punctuation">)</span>
<span class="token punctuation">)</span> <span class="token keyword">ENGINE</span> <span class="token operator">=</span> InnoDB<span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   The definition includes no explicit primary key, but the index
        on
   <code class="literal">
    NOT NULL
   </code>
   column
   <code class="literal">
    j
   </code>
   places the same constraint on rows as a primary key and cannot
        be made invisible:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa19284679"><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">ALTER</span> <span class="token keyword">TABLE</span> t2 <span class="token keyword">ALTER</span> <span class="token keyword">INDEX</span> j_idx <span class="token keyword">INVISIBLE</span><span class="token punctuation">;</span>
<span class="token output">ERROR 3522 (HY000)<span class="token punctuation">:</span> A primary key index cannot be invisible.</span></code></pre>
  </div>
  <p>
   Now suppose that an explicit primary key is added to the table:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa7222166"><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">ALTER</span> <span class="token keyword">TABLE</span> t2 <span class="token keyword">ADD</span> <span class="token keyword">PRIMARY</span> <span class="token keyword">KEY</span> <span class="token punctuation">(</span>i<span class="token punctuation">)</span><span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   The explicit primary key cannot be made invisible. In addition,
        the unique index on
   <code class="literal">
    j
   </code>
   no longer acts as an
        implicit primary key and as a result can be made invisible:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa61652479"><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">ALTER</span> <span class="token keyword">TABLE</span> t2 <span class="token keyword">ALTER</span> <span class="token keyword">INDEX</span> j_idx <span class="token keyword">INVISIBLE</span><span class="token punctuation">;</span>
<span class="token output">Query OK, 0 rows affected (0.03 sec)</span></code></pre>
  </div>
 </div>
 <br/>
</div>
