<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h3 class="title">
      <a name="information-schema-statistics-table">
      </a>
      28.3.34 The INFORMATION_SCHEMA STATISTICS Table
     </h3>
    </div>
   </div>
  </div>
  <a class="indexterm" name="idm46045078684800">
  </a>
  <p>
   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 provides
      information about table indexes.
  </p>
  <p>
   Columns in
   <a class="link" href="information-schema-statistics-table.html" title="28.3.34 The INFORMATION_SCHEMA STATISTICS Table">
    <code class="literal">
     STATISTICS
    </code>
   </a>
   that represent
      table statistics hold cached values. The
   <a class="link" href="server-system-variables.html#sysvar_information_schema_stats_expiry">
    <code class="literal">
     information_schema_stats_expiry
    </code>
   </a>
   system variable defines the period of time before cached table
      statistics expire. The default is 86400 seconds (24 hours). If
      there are no cached statistics or statistics have expired,
      statistics are retrieved from storage engines when querying table
      statistics columns. To update cached values at any time for a
      given table, use
   <a class="link" href="analyze-table.html" title="15.7.3.1 ANALYZE TABLE Statement">
    <code class="literal">
     ANALYZE TABLE
    </code>
   </a>
   . To
      always retrieve the latest statistics directly from storage
      engines, set
   <a class="link" href="server-system-variables.html#sysvar_information_schema_stats_expiry">
    <code class="literal">
     information_schema_stats_expiry=0
    </code>
   </a>
   .
      For more information, see
   <a class="xref" href="information-schema-optimization.html" title="10.2.3 Optimizing INFORMATION_SCHEMA Queries">
    Section 10.2.3, “Optimizing INFORMATION_SCHEMA Queries”
   </a>
   .
  </p>
  <div class="note" style="margin-left: 0.5in; margin-right: 0.5in;">
   <div class="admon-title">
    Note
   </div>
   <p>
    If the
    <a class="link" href="innodb-parameters.html#sysvar_innodb_read_only">
     <code class="literal">
      innodb_read_only
     </code>
    </a>
    system
        variable is enabled,
    <a class="link" href="analyze-table.html" title="15.7.3.1 ANALYZE TABLE Statement">
     <code class="literal">
      ANALYZE
        TABLE
     </code>
    </a>
    may fail because it cannot update statistics
        tables in the data dictionary, which use
    <code class="literal">
     InnoDB
    </code>
    . For
    <a class="link" href="analyze-table.html" title="15.7.3.1 ANALYZE TABLE Statement">
     <code class="literal">
      ANALYZE
        TABLE
     </code>
    </a>
    operations that update the key distribution,
        failure may occur even if the operation updates the table itself
        (for example, if it is a
    <code class="literal">
     MyISAM
    </code>
    table). To
        obtain the updated distribution statistics, set
    <a class="link" href="server-system-variables.html#sysvar_information_schema_stats_expiry">
     <code class="literal">
      information_schema_stats_expiry=0
     </code>
    </a>
    .
   </p>
  </div>
  <p>
   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 has these
      columns:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      <code class="literal">
       TABLE_CATALOG
      </code>
     </p>
     <p>
      The name of the catalog to which the table containing the
          index belongs. This value is always
      <code class="literal">
       def
      </code>
      .
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       TABLE_SCHEMA
      </code>
     </p>
     <p>
      The name of the schema (database) to which the table
          containing the index belongs.
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       TABLE_NAME
      </code>
     </p>
     <p>
      The name of the table containing the index.
     </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">
       INDEX_SCHEMA
      </code>
     </p>
     <p>
      The name of the schema (database) to which the index belongs.
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       INDEX_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>
     <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>
     <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">
       NULLABLE
      </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">
       IS_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>
      ), which
          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>
  <h4>
   <a name="idm46045078589488">
   </a>
   Notes
  </h4>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      There is no standard
      <code class="literal">
       INFORMATION_SCHEMA
      </code>
      table for indexes. The MySQL column list is similar to what
          SQL Server 2000 returns for
      <code class="literal">
       sp_statistics
      </code>
      ,
          except that
      <code class="literal">
       QUALIFIER
      </code>
      and
      <code class="literal">
       OWNER
      </code>
      are replaced with
      <code class="literal">
       CATALOG
      </code>
      and
      <code class="literal">
       SCHEMA
      </code>
      ,
          respectively.
     </p>
    </li>
   </ul>
  </div>
  <p>
   Information about table indexes is also available from the
   <a class="link" href="show-index.html" title="15.7.7.23 SHOW INDEX Statement">
    <code class="literal">
     SHOW INDEX
    </code>
   </a>
   statement. See
   <a class="xref" href="show-index.html" title="15.7.7.23 SHOW INDEX Statement">
    Section 15.7.7.23, “SHOW INDEX Statement”
   </a>
   . The following statements are
      equivalent:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa31883238"><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> INFORMATION_SCHEMA<span class="token punctuation">.</span>STATISTICS
  <span class="token keyword">WHERE</span> <span class="token keyword">table_name</span> <span class="token operator">=</span> <span class="token string">'<em class="replaceable">tbl_name</em>'</span>
  <span class="token operator">AND</span> table_schema <span class="token operator">=</span> <span class="token string">'<em class="replaceable">db_name</em>'</span>

<span class="token keyword">SHOW</span> <span class="token keyword">INDEX</span>
  <span class="token keyword">FROM</span> <em class="replaceable">tbl_name</em>
  <span class="token keyword">FROM</span> <em class="replaceable">db_name</em></code></pre>
  </div>
  <p>
   Information about generated invisible primary key columns is
      visible in this table by default. You can cause such information
      to be hidden 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>
