<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h4 class="title">
      <a name="show-index">
      </a>
      15.7.7.23 SHOW INDEX Statement
     </h4>
    </div>
   </div>
  </div>
  <a class="indexterm" name="idm46045170215040">
  </a>
  <a class="indexterm" name="idm46045170213136">
  </a>
  <a class="indexterm" name="idm46045170212064">
  </a>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa16796050"><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">SHOW</span> <span class="token punctuation">[</span><span class="token keyword">EXTENDED</span><span class="token punctuation">]</span> {<span class="token keyword">INDEX</span> <span class="token operator">|</span> <span class="token keyword">INDEXES</span> <span class="token operator">|</span> <span class="token keyword">KEYS</span>}
    {<span class="token keyword">FROM</span> <span class="token operator">|</span> <span class="token keyword">IN</span>} <em class="replaceable">tbl_name</em>
    <span class="token punctuation">[</span>{<span class="token keyword">FROM</span> <span class="token operator">|</span> <span class="token keyword">IN</span>} <em class="replaceable">db_name</em><span class="token punctuation">]</span>
    <span class="token punctuation">[</span><span class="token keyword">WHERE</span> <em class="replaceable">expr</em><span class="token punctuation">]</span></code></pre>
  </div>
  <p>
   <a class="link" href="show-index.html" title="15.7.7.23 SHOW INDEX Statement">
    <code class="literal">
     SHOW INDEX
    </code>
   </a>
   returns table index
        information. The format resembles that of the
   <code class="literal">
    SQLStatistics
   </code>
   call in ODBC. This statement
        requires some privilege for any column in the table.
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa99024230"><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">SHOW</span> <span class="token keyword">INDEX</span> <span class="token keyword">FROM</span> City\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>
        Table<span class="token punctuation">:</span> city
   Non_unique<span class="token punctuation">:</span> 0
     Key_name<span class="token punctuation">:</span> PRIMARY
 Seq_in_index<span class="token punctuation">:</span> 1
  Column_name<span class="token punctuation">:</span> ID
    Collation<span class="token punctuation">:</span> A
  Cardinality<span class="token punctuation">:</span> 4188
     Sub_part<span class="token punctuation">:</span> NULL
       Packed<span class="token punctuation">:</span> NULL
         Null<span class="token punctuation">:</span>
   Index_type<span class="token punctuation">:</span> BTREE
      Comment<span class="token punctuation">:</span>
Index_comment<span class="token punctuation">:</span>
      Visible<span class="token punctuation">:</span> YES
   Expression<span class="token punctuation">:</span> NULL
<span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span 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>
        Table<span class="token punctuation">:</span> city
   Non_unique<span class="token punctuation">:</span> 1
     Key_name<span class="token punctuation">:</span> CountryCode
 Seq_in_index<span class="token punctuation">:</span> 1
  Column_name<span class="token punctuation">:</span> CountryCode
    Collation<span class="token punctuation">:</span> A
  Cardinality<span class="token punctuation">:</span> 232
     Sub_part<span class="token punctuation">:</span> NULL
       Packed<span class="token punctuation">:</span> NULL
         Null<span class="token punctuation">:</span>
   Index_type<span class="token punctuation">:</span> BTREE
      Comment<span class="token punctuation">:</span>
Index_comment<span class="token punctuation">:</span>
      Visible<span class="token punctuation">:</span> YES
   Expression<span class="token punctuation">:</span> NULL</span></code></pre>
  </div>
  <p>
   An alternative to
   <code class="literal">
    <em class="replaceable">
     <code>
      tbl_name
     </code>
    </em>
    FROM
    <em class="replaceable">
     <code>
      db_name
     </code>
    </em>
   </code>
   syntax is
   <em class="replaceable">
    <code>
     db_name
    </code>
   </em>
   .
   <em class="replaceable">
    <code>
     tbl_name
    </code>
   </em>
   .
        These two statements are equivalent:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa25708027"><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">SHOW</span> <span class="token keyword">INDEX</span> <span class="token keyword">FROM</span> mytable <span class="token keyword">FROM</span> mydb<span class="token punctuation">;</span>
<span class="token keyword">SHOW</span> <span class="token keyword">INDEX</span> <span class="token keyword">FROM</span> mydb<span class="token punctuation">.</span>mytable<span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   The optional
   <code class="literal">
    EXTENDED
   </code>
   keyword causes the
        output to include information about hidden indexes that MySQL
        uses internally and are not accessible by users.
  </p>
  <p>
   The
   <code class="literal">
    WHERE
   </code>
   clause can be given to select rows
        using more general conditions, as discussed in
   <a class="xref" href="extended-show.html" title="28.8 Extensions to SHOW Statements">
    Section 28.8, “Extensions to SHOW Statements”
   </a>
   .
  </p>
  <p>
   <a class="link" href="show-index.html" title="15.7.7.23 SHOW INDEX Statement">
    <code class="literal">
     SHOW INDEX
    </code>
   </a>
   returns the following
        fields:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      <code class="literal">
       Table
      </code>
     </p>
     <p>
      The name of the table.
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       Non_unique
      </code>
     </p>
     <p>
      0 if the index cannot contain duplicates, 1 if it can.
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       Key_name
      </code>
     </p>
     <p>
      The name of the index. If the index is the primary key, the
            name is always
      <code class="literal">
       PRIMARY
      </code>
      .
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       Seq_in_index
      </code>
     </p>
     <p>
      The column sequence number in the index, starting with 1.
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       Column_name
      </code>
     </p>
     <p>
      The column name. See also the description for the
      <code class="literal">
       Expression
      </code>
      column.
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       Collation
      </code>
     </p>
     <a class="indexterm" name="idm46045170180224">
     </a>
     <p>
      How the column is sorted in the index. This can have values
      <code class="literal">
       A
      </code>
      (ascending),
      <code class="literal">
       D
      </code>
      (descending), or
      <code class="literal">
       NULL
      </code>
      (not sorted).
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       Cardinality
      </code>
     </p>
     <a class="indexterm" name="idm46045170174256">
     </a>
     <a class="indexterm" name="idm46045170172352">
     </a>
     <p>
      An estimate of the number of unique values in the index. To
            update this number, run
      <a class="link" href="analyze-table.html" title="15.7.3.1 ANALYZE TABLE Statement">
       <code class="literal">
        ANALYZE
            TABLE
       </code>
      </a>
      or (for
      <code class="literal">
       MyISAM
      </code>
      tables)
      <a class="link" href="myisamchk.html" title="6.6.4 myisamchk — MyISAM Table-Maintenance Utility">
       <span class="command">
        <strong>
         myisamchk -a
        </strong>
       </span>
      </a>
      .
     </p>
     <p>
      <code class="literal">
       Cardinality
      </code>
      is counted based on
            statistics stored as integers, so the value is not
            necessarily exact even for small tables. The higher the
            cardinality, the greater the chance that MySQL uses the
            index when doing joins.
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       Sub_part
      </code>
     </p>
     <p>
      The index prefix. That is, the number of indexed characters
            if the column is only partly indexed,
      <code class="literal">
       NULL
      </code>
      if the entire column is indexed.
     </p>
     <div class="note" style="margin-left: 0.5in; margin-right: 0.5in;">
      <div class="admon-title">
       Note
      </div>
      <p>
       Prefix
       <span class="emphasis">
        <em>
         limits
        </em>
       </span>
       are measured in bytes.
              However, prefix
       <span class="emphasis">
        <em>
         lengths
        </em>
       </span>
       for index
              specifications in
       <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="alter-table.html" title="15.1.9 ALTER TABLE Statement">
        <code class="literal">
         ALTER
              TABLE
        </code>
       </a>
       , and
       <a class="link" href="create-index.html" title="15.1.15 CREATE INDEX Statement">
        <code class="literal">
         CREATE
              INDEX
        </code>
       </a>
       statements are interpreted as number of
              characters for nonbinary string types
              (
       <a class="link" href="char.html" title="13.3.2 The CHAR and VARCHAR Types">
        <code class="literal">
         CHAR
        </code>
       </a>
       ,
       <a class="link" href="char.html" title="13.3.2 The CHAR and VARCHAR Types">
        <code class="literal">
         VARCHAR
        </code>
       </a>
       ,
       <a class="link" href="blob.html" title="13.3.4 The BLOB and TEXT Types">
        <code class="literal">
         TEXT
        </code>
       </a>
       ) and number of bytes
              for binary string types
              (
       <a class="link" href="binary-varbinary.html" title="13.3.3 The BINARY and VARBINARY Types">
        <code class="literal">
         BINARY
        </code>
       </a>
       ,
       <a class="link" href="binary-varbinary.html" title="13.3.3 The BINARY and VARBINARY Types">
        <code class="literal">
         VARBINARY
        </code>
       </a>
       ,
       <a class="link" href="blob.html" title="13.3.4 The BLOB and TEXT Types">
        <code class="literal">
         BLOB
        </code>
       </a>
       ). Take this into
              account when specifying a prefix length for a nonbinary
              string column that uses a multibyte character set.
      </p>
     </div>
     <p>
      For additional information about index prefixes, see
      <a class="xref" href="column-indexes.html" title="10.3.5 Column Indexes">
       Section 10.3.5, “Column Indexes”
      </a>
      , and
      <a class="xref" href="create-index.html" title="15.1.15 CREATE INDEX Statement">
       Section 15.1.15, “CREATE INDEX Statement”
      </a>
      .
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       Packed
      </code>
     </p>
     <p>
      Indicates how the key is packed.
      <code class="literal">
       NULL
      </code>
      if
            it is not.
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       Null
      </code>
     </p>
     <p>
      Contains
      <code class="literal">
       YES
      </code>
      if the column may contain
      <code class="literal">
       NULL
      </code>
      values and
      <code class="literal">
       ''
      </code>
      if
            not.
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       Index_type
      </code>
     </p>
     <p>
      The index method used (
      <code class="literal">
       BTREE
      </code>
      ,
      <code class="literal">
       FULLTEXT
      </code>
      ,
      <code class="literal">
       HASH
      </code>
      ,
      <code class="literal">
       RTREE
      </code>
      ).
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       Comment
      </code>
     </p>
     <p>
      Information about the index not described in its own column,
            such as
      <code class="literal">
       disabled
      </code>
      if the index is
            disabled.
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       Index_comment
      </code>
     </p>
     <p>
      Any comment provided for the index with a
      <code class="literal">
       COMMENT
      </code>
      attribute when the index was
            created.
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       Visible
      </code>
     </p>
     <p>
      Whether the index is visible to the optimizer. See
      <a class="xref" href="invisible-indexes.html" title="10.3.12 Invisible Indexes">
       Section 10.3.12, “Invisible Indexes”
      </a>
      .
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       Expression
      </code>
     </p>
     <p>
      MySQL supports functional key parts (see
      <a class="xref" href="create-index.html#create-index-functional-key-parts" title="Functional Key Parts">
       Functional Key Parts
      </a>
      ); this
            affects both the
      <code class="literal">
       Column_name
      </code>
      and
      <code class="literal">
       Expression
      </code>
      columns:
     </p>
     <div class="itemizedlist">
      <ul class="itemizedlist" style="list-style-type: circle; ">
       <li class="listitem">
        <p>
         For a nonfunctional key part,
         <code class="literal">
          Column_name
         </code>
         indicates the column
                indexed by the key part and
         <code class="literal">
          Expression
         </code>
         is
         <code class="literal">
          NULL
         </code>
         .
        </p>
       </li>
       <li class="listitem">
        <p>
         For a functional key part,
         <code class="literal">
          Column_name
         </code>
         column is
         <code class="literal">
          NULL
         </code>
         and
         <code class="literal">
          Expression
         </code>
         indicates the expression
                for the key part.
        </p>
       </li>
      </ul>
     </div>
    </li>
   </ul>
  </div>
  <p>
   Information about table indexes is also available from the
   <code class="literal">
    INFORMATION_SCHEMA
   </code>
   <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. See
   <a class="xref" href="information-schema-statistics-table.html" title="28.3.34 The INFORMATION_SCHEMA STATISTICS Table">
    Section 28.3.34, “The INFORMATION_SCHEMA STATISTICS Table”
   </a>
   . The
        extended information about hidden indexes is available only
        using
   <code class="literal">
    SHOW EXTENDED INDEX
   </code>
   ; it cannot be
        obtained from the
   <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>
   You can list a table's indexes with the
   <a class="link" href="mysqlshow.html" title="6.5.6 mysqlshow — Display Database, Table, and Column Information">
    <span class="command">
     <strong>
      mysqlshow -k
      <em class="replaceable">
       <code>
        db_name
       </code>
      </em>
      <em class="replaceable">
       <code>
        tbl_name
       </code>
      </em>
     </strong>
    </span>
   </a>
   command.
  </p>
  <p>
   <code class="literal">
    SHOW INDEX
   </code>
   includes the table's
        generated invisible key, if it has one, by default. You can
        cause this information to be suppressed in the statement's
        output by setting
   <a class="link" href="server-system-variables.html#sysvar_show_gipk_in_create_table_and_information_schema">
    <code class="literal">
     show_gipk_in_create_table_and_information_schema
        = OFF
    </code>
   </a>
   . For more information, see
   <a class="xref" href="create-table-gipks.html" title="15.1.20.11 Generated Invisible Primary Keys">
    Section 15.1.20.11, “Generated Invisible Primary Keys”
   </a>
   .
  </p>
 </div>
 <br/>
</div>
