<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h3 class="title">
      <a name="information-schema-innodb-tablestats-table">
      </a>
      28.4.26 The INFORMATION_SCHEMA INNODB_TABLESTATS View
     </h3>
    </div>
   </div>
  </div>
  <a class="indexterm" name="idm46045076337216">
  </a>
  <p>
   The
   <a class="link" href="information-schema-innodb-tablestats-table.html" title="28.4.26 The INFORMATION_SCHEMA INNODB_TABLESTATS View">
    <code class="literal">
     INNODB_TABLESTATS
    </code>
   </a>
   table provides
      a view of low-level status information about
   <code class="literal">
    InnoDB
   </code>
   tables. This data is used by the MySQL
      optimizer to calculate which index to use when querying an
   <code class="literal">
    InnoDB
   </code>
   table. This information is derived from
      in-memory data structures rather than data stored on disk. There
      is no corresponding internal
   <code class="literal">
    InnoDB
   </code>
   system
      table.
  </p>
  <p>
   <code class="literal">
    InnoDB
   </code>
   tables are represented in this view if
      they have been opened since the last server restart and have not
      aged out of the table cache. Tables for which persistent stats are
      available are always represented in this view.
  </p>
  <p>
   Table statistics are updated only for
   <a class="link" href="delete.html" title="15.2.2 DELETE Statement">
    <code class="literal">
     DELETE
    </code>
   </a>
   or
   <a class="link" href="update.html" title="15.2.17 UPDATE Statement">
    <code class="literal">
     UPDATE
    </code>
   </a>
   operations that modify
      indexed columns. Statistics are not updated by operations that
      modify only nonindexed columns.
  </p>
  <p>
   <a class="link" href="analyze-table.html" title="15.7.3.1 ANALYZE TABLE Statement">
    <code class="literal">
     ANALYZE TABLE
    </code>
   </a>
   clears table
      statistics and sets the
   <code class="literal">
    STATS_INITIALIZED
   </code>
   column to
   <code class="literal">
    Uninitialized
   </code>
   . Statistics are
      collected again the next time the table is accessed.
  </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-tablestats-table.html" title="28.4.26 The INFORMATION_SCHEMA INNODB_TABLESTATS View">
    <code class="literal">
     INNODB_TABLESTATS
    </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_ID
      </code>
     </p>
     <p>
      An identifier representing the table for which statistics are
          available; the same value as
      <code class="literal">
       INNODB_TABLES.TABLE_ID
      </code>
      .
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       NAME
      </code>
     </p>
     <p>
      The name of the table; the same value as
      <code class="literal">
       INNODB_TABLES.NAME
      </code>
      .
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       STATS_INITIALIZED
      </code>
     </p>
     <p>
      The value is
      <code class="literal">
       Initialized
      </code>
      if the statistics
          are already collected,
      <code class="literal">
       Uninitialized
      </code>
      if
          not.
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       NUM_ROWS
      </code>
     </p>
     <p>
      The current estimated number of rows in the table. Updated
          after each DML operation. The value could be imprecise if
          uncommitted transactions are inserting into or deleting from
          the table.
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       CLUST_INDEX_SIZE
      </code>
     </p>
     <p>
      The number of pages on disk that store the clustered index,
          which holds the
      <code class="literal">
       InnoDB
      </code>
      table data in
          primary key order. This value might be null if no statistics
          are collected yet for the table.
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       OTHER_INDEX_SIZE
      </code>
     </p>
     <p>
      The number of pages on disk that store all secondary indexes
          for the table. This value might be null if no statistics are
          collected yet for the table.
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       MODIFIED_COUNTER
      </code>
     </p>
     <p>
      The number of rows modified by DML operations, such as
      <code class="literal">
       INSERT
      </code>
      ,
      <code class="literal">
       UPDATE
      </code>
      ,
      <code class="literal">
       DELETE
      </code>
      , and also cascade operations from
          foreign keys. This column is reset each time table statistics
          are recalculated
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       AUTOINC
      </code>
     </p>
     <p>
      The next number to be issued for any auto-increment-based
          operation. The rate at which the
      <code class="literal">
       AUTOINC
      </code>
      value changes depends on how many times auto-increment numbers
          have been requested and how many numbers are granted per
          request.
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       REF_COUNT
      </code>
     </p>
     <p>
      When this counter reaches zero, the table metadata can be
          evicted from the table cache.
     </p>
    </li>
   </ul>
  </div>
  <h4>
   <a name="idm46045076296080">
   </a>
   Example
  </h4>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa36130149"><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_TABLESTATS <span class="token keyword">where</span> TABLE_ID <span class="token operator">=</span> <span class="token number">71</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>
         TABLE_ID<span class="token punctuation">:</span> 71
             NAME<span class="token punctuation">:</span> test/t1
STATS_INITIALIZED<span class="token punctuation">:</span> Initialized
         NUM_ROWS<span class="token punctuation">:</span> 1
 CLUST_INDEX_SIZE<span class="token punctuation">:</span> 1
 OTHER_INDEX_SIZE<span class="token punctuation">:</span> 0
 MODIFIED_COUNTER<span class="token punctuation">:</span> 1
          AUTOINC<span class="token punctuation">:</span> 0
        REF_COUNT<span class="token punctuation">:</span> 1</span></code></pre>
  </div>
  <h4>
   <a name="idm46045076293424">
   </a>
   Notes
  </h4>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      This table is useful primarily for expert-level performance
          monitoring, or when developing performance-related extensions
          for MySQL.
     </p>
    </li>
    <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>
