<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h3 class="title">
      <a name="information-schema-innodb-indexes-table">
      </a>
      28.4.20 The INFORMATION_SCHEMA INNODB_INDEXES Table
     </h3>
    </div>
   </div>
  </div>
  <a class="indexterm" name="idm46045076683488">
  </a>
  <p>
   The
   <a class="link" href="information-schema-innodb-indexes-table.html" title="28.4.20 The INFORMATION_SCHEMA INNODB_INDEXES Table">
    <code class="literal">
     INNODB_INDEXES
    </code>
   </a>
   table provides
      metadata about
   <code class="literal">
    InnoDB
   </code>
   indexes.
  </p>
  <p>
   For related usage information and examples, see
   <a class="xref" href="innodb-information-schema-system-tables.html" title="17.15.3 InnoDB INFORMATION_SCHEMA Schema Object Tables">
    Section 17.15.3, “InnoDB INFORMATION_SCHEMA Schema Object Tables”
   </a>
   .
  </p>
  <p>
   The
   <a class="link" href="information-schema-innodb-indexes-table.html" title="28.4.20 The INFORMATION_SCHEMA INNODB_INDEXES Table">
    <code class="literal">
     INNODB_INDEXES
    </code>
   </a>
   table has these
      columns:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      <code class="literal">
       INDEX_ID
      </code>
     </p>
     <p>
      An identifier for the index. Index identifiers are unique
          across all the databases in an instance.
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       NAME
      </code>
     </p>
     <p>
      The name of the index. Most indexes created implicitly by
      <code class="literal">
       InnoDB
      </code>
      have consistent names but the index
          names are not necessarily unique. Examples:
      <code class="literal">
       PRIMARY
      </code>
      for a primary key index,
      <code class="literal">
       GEN_CLUST_INDEX
      </code>
      for the index representing
          a primary key when one is not specified, and
      <code class="literal">
       ID_IND
      </code>
      ,
      <code class="literal">
       FOR_IND
      </code>
      , and
      <code class="literal">
       REF_IND
      </code>
      for foreign key constraints.
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       TABLE_ID
      </code>
     </p>
     <p>
      An identifier representing the table associated with the
          index; the same value as
      <code class="literal">
       INNODB_TABLES.TABLE_ID
      </code>
      .
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       TYPE
      </code>
     </p>
     <p>
      A numeric value derived from bit-level information that
          identifies the index type. 0 = nonunique secondary index; 1 =
          automatically generated clustered index
          (
      <code class="literal">
       GEN_CLUST_INDEX
      </code>
      ); 2 = unique nonclustered
          index; 3 = clustered index; 32 = full-text index; 64 = spatial
          index; 128 = secondary index on a
      <a class="link" href="glossary.html#glos_virtual_generated_column" title="virtual generated column">
       virtual
          generated column
      </a>
      .
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       N_FIELDS
      </code>
     </p>
     <p>
      The number of columns in the index key. For
      <code class="literal">
       GEN_CLUST_INDEX
      </code>
      indexes, this value is 0
          because the index is created using an artificial value rather
          than a real table column.
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       PAGE_NO
      </code>
     </p>
     <p>
      The root page number of the index B-tree. For full-text
          indexes, the
      <code class="literal">
       PAGE_NO
      </code>
      column is unused and
          set to -1 (
      <code class="literal">
       FIL_NULL
      </code>
      ) because the full-text
          index is laid out in several B-trees (auxiliary tables).
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       SPACE
      </code>
     </p>
     <p>
      An identifier for the tablespace where the index resides. 0
          means the
      <code class="literal">
       InnoDB
      </code>
      <a class="link" href="glossary.html#glos_system_tablespace" title="system tablespace">
       system
          tablespace
      </a>
      . Any other number represents a table created
          with a separate
      <code class="literal">
       .ibd
      </code>
      file in
      <a class="link" href="glossary.html#glos_file_per_table" title="file-per-table">
       file-per-table
      </a>
      mode. This identifier stays the same after a
      <a class="link" href="truncate-table.html" title="15.1.37 TRUNCATE TABLE Statement">
       <code class="literal">
        TRUNCATE TABLE
       </code>
      </a>
      statement.
          Because all indexes for a table reside in the same tablespace
          as the table, this value is not necessarily unique.
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       MERGE_THRESHOLD
      </code>
     </p>
     <p>
      The merge threshold value for index pages. If the amount of
          data in an index page falls below the
      <a class="link" href="index-page-merge-threshold.html" title="17.8.11 Configuring the Merge Threshold for Index Pages">
       <code class="literal">
        MERGE_THRESHOLD
       </code>
      </a>
      value when a row is deleted or when a row is shortened by an
          update operation,
      <code class="literal">
       InnoDB
      </code>
      attempts to merge
          the index page with the neighboring index page. The default
          threshold value is 50%. For more information, see
      <a class="xref" href="index-page-merge-threshold.html" title="17.8.11 Configuring the Merge Threshold for Index Pages">
       Section 17.8.11, “Configuring the Merge Threshold for Index Pages”
      </a>
      .
     </p>
    </li>
   </ul>
  </div>
  <h4>
   <a name="idm46045076643728">
   </a>
   Example
  </h4>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa45937518"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token prompt">mysql&gt;</span> <span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> INFORMATION_SCHEMA<span class="token punctuation">.</span>INNODB_INDEXES <span class="token keyword">WHERE</span> TABLE_ID <span class="token operator">=</span> <span class="token number">34</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>
       INDEX_ID<span class="token punctuation">:</span> 39
           NAME<span class="token punctuation">:</span> GEN_CLUST_INDEX
       TABLE_ID<span class="token punctuation">:</span> 34
           TYPE<span class="token punctuation">:</span> 1
       N_FIELDS<span class="token punctuation">:</span> 0
        PAGE_NO<span class="token punctuation">:</span> 3
          SPACE<span class="token punctuation">:</span> 23
MERGE_THRESHOLD<span class="token punctuation">:</span> 50
<span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span> 2. 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>
       INDEX_ID<span class="token punctuation">:</span> 40
           NAME<span class="token punctuation">:</span> i1
       TABLE_ID<span class="token punctuation">:</span> 34
           TYPE<span class="token punctuation">:</span> 0
       N_FIELDS<span class="token punctuation">:</span> 1
        PAGE_NO<span class="token punctuation">:</span> 4
          SPACE<span class="token punctuation">:</span> 23
MERGE_THRESHOLD<span class="token punctuation">:</span> 50</span></code></pre>
  </div>
  <h4>
   <a name="idm46045076640912">
   </a>
   Notes
  </h4>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      You must have the
      <a class="link" href="privileges-provided.html#priv_process">
       <code class="literal">
        PROCESS
       </code>
      </a>
      privilege to query this table.
     </p>
    </li>
    <li class="listitem">
     <p>
      Use the
      <code class="literal">
       INFORMATION_SCHEMA
      </code>
      <a class="link" href="information-schema-columns-table.html" title="28.3.8 The INFORMATION_SCHEMA COLUMNS Table">
       <code class="literal">
        COLUMNS
       </code>
      </a>
      table or the
      <a class="link" href="show-columns.html" title="15.7.7.6 SHOW COLUMNS Statement">
       <code class="literal">
        SHOW COLUMNS
       </code>
      </a>
      statement to view
          additional information about the columns of this table,
          including data types and default values.
     </p>
    </li>
   </ul>
  </div>
 </div>
 <br/>
</div>
