<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h3 class="title">
      <a name="information-schema-innodb-cmp-per-index-table">
      </a>
      28.4.8 The INFORMATION_SCHEMA INNODB_CMP_PER_INDEX and
INNODB_CMP_PER_INDEX_RESET Tables
     </h3>
    </div>
   </div>
  </div>
  <a class="indexterm" name="idm46045077207360">
  </a>
  <a class="indexterm" name="idm46045077205856">
  </a>
  <p>
   The
   <a class="link" href="information-schema-innodb-cmp-per-index-table.html" title="28.4.8 The INFORMATION_SCHEMA INNODB_CMP_PER_INDEX and INNODB_CMP_PER_INDEX_RESET Tables">
    <code class="literal">
     INNODB_CMP_PER_INDEX
    </code>
   </a>
   and
   <a class="link" href="information-schema-innodb-cmp-per-index-table.html" title="28.4.8 The INFORMATION_SCHEMA INNODB_CMP_PER_INDEX and INNODB_CMP_PER_INDEX_RESET Tables">
    <code class="literal">
     INNODB_CMP_PER_INDEX_RESET
    </code>
   </a>
   tables
      provide status information on operations related to
   <a class="link" href="glossary.html#glos_compression" title="compression">
    compressed
   </a>
   <code class="literal">
    InnoDB
   </code>
   tables and indexes, with separate
      statistics for each combination of database, table, and index, to
      help you evaluate the performance and usefulness of compression
      for specific tables.
  </p>
  <p>
   For a compressed
   <code class="literal">
    InnoDB
   </code>
   table, both the table
      data and all the
   <a class="link" href="glossary.html#glos_secondary_index" title="secondary index">
    secondary
      indexes
   </a>
   are compressed. In this context, the table data is
      treated as just another index, one that happens to contain all the
      columns: the
   <a class="link" href="glossary.html#glos_clustered_index" title="clustered index">
    clustered
      index
   </a>
   .
  </p>
  <p>
   The
   <a class="link" href="information-schema-innodb-cmp-per-index-table.html" title="28.4.8 The INFORMATION_SCHEMA INNODB_CMP_PER_INDEX and INNODB_CMP_PER_INDEX_RESET Tables">
    <code class="literal">
     INNODB_CMP_PER_INDEX
    </code>
   </a>
   and
   <a class="link" href="information-schema-innodb-cmp-per-index-table.html" title="28.4.8 The INFORMATION_SCHEMA INNODB_CMP_PER_INDEX and INNODB_CMP_PER_INDEX_RESET Tables">
    <code class="literal">
     INNODB_CMP_PER_INDEX_RESET
    </code>
   </a>
   tables
      have these columns:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      <code class="literal">
       DATABASE_NAME
      </code>
     </p>
     <p>
      The schema (database) containing the applicable table.
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       TABLE_NAME
      </code>
     </p>
     <p>
      The table to monitor for compression statistics.
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       INDEX_NAME
      </code>
     </p>
     <p>
      The index to monitor for compression statistics.
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       COMPRESS_OPS
      </code>
     </p>
     <p>
      The number of compression operations attempted.
      <a class="link" href="glossary.html#glos_page" title="page">
       Pages
      </a>
      are compressed whenever
          an empty page is created or the space for the uncompressed
          modification log runs out.
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       COMPRESS_OPS_OK
      </code>
     </p>
     <p>
      The number of successful compression operations. Subtract from
          the
      <code class="literal">
       COMPRESS_OPS
      </code>
      value to get the number of
      <a class="link" href="glossary.html#glos_compression_failure" title="compression failure">
       compression
          failures
      </a>
      . Divide by the
      <code class="literal">
       COMPRESS_OPS
      </code>
      value to get the percentage of compression failures.
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       COMPRESS_TIME
      </code>
     </p>
     <p>
      The total time in seconds used for compressing data in this
          index.
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       UNCOMPRESS_OPS
      </code>
     </p>
     <p>
      The number of uncompression operations performed. Compressed
      <code class="literal">
       InnoDB
      </code>
      pages are uncompressed whenever
          compression
      <a class="link" href="glossary.html#glos_compression_failure" title="compression failure">
       fails
      </a>
      , or the
          first time a compressed page is accessed in the
      <a class="link" href="glossary.html#glos_buffer_pool" title="buffer pool">
       buffer pool
      </a>
      and the
          uncompressed page does not exist.
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       UNCOMPRESS_TIME
      </code>
     </p>
     <p>
      The total time in seconds used for uncompressing data in this
          index.
     </p>
    </li>
   </ul>
  </div>
  <h4>
   <a name="idm46045077171712">
   </a>
   Example
  </h4>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa8194516"><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_CMP_PER_INDEX\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>
  database_name<span class="token punctuation">:</span> employees
     table_name<span class="token punctuation">:</span> salaries
     index_name<span class="token punctuation">:</span> PRIMARY
   compress_ops<span class="token punctuation">:</span> 0
compress_ops_ok<span class="token punctuation">:</span> 0
  compress_time<span class="token punctuation">:</span> 0
 uncompress_ops<span class="token punctuation">:</span> 23451
uncompress_time<span class="token punctuation">:</span> 4
<span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span 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>
  database_name<span class="token punctuation">:</span> employees
     table_name<span class="token punctuation">:</span> salaries
     index_name<span class="token punctuation">:</span> emp_no
   compress_ops<span class="token punctuation">:</span> 0
compress_ops_ok<span class="token punctuation">:</span> 0
  compress_time<span class="token punctuation">:</span> 0
 uncompress_ops<span class="token punctuation">:</span> 1597
uncompress_time<span class="token punctuation">:</span> 0</span></code></pre>
  </div>
  <h4>
   <a name="idm46045077168944">
   </a>
   Notes
  </h4>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      Use these tables to measure the effectiveness of
      <code class="literal">
       InnoDB
      </code>
      table
      <a class="link" href="glossary.html#glos_compression" title="compression">
       compression
      </a>
      for
          specific tables, indexes, or both.
     </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 these tables.
     </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 these tables,
          including data types and default values.
     </p>
    </li>
    <li class="listitem">
     <p>
      Because collecting separate measurements for every index
          imposes substantial performance overhead,
      <a class="link" href="information-schema-innodb-cmp-per-index-table.html" title="28.4.8 The INFORMATION_SCHEMA INNODB_CMP_PER_INDEX and INNODB_CMP_PER_INDEX_RESET Tables">
       <code class="literal">
        INNODB_CMP_PER_INDEX
       </code>
      </a>
      and
      <a class="link" href="information-schema-innodb-cmp-per-index-table.html" title="28.4.8 The INFORMATION_SCHEMA INNODB_CMP_PER_INDEX and INNODB_CMP_PER_INDEX_RESET Tables">
       <code class="literal">
        INNODB_CMP_PER_INDEX_RESET
       </code>
      </a>
      statistics are not gathered by default. You must enable the
      <a class="link" href="innodb-parameters.html#sysvar_innodb_cmp_per_index_enabled">
       <code class="literal">
        innodb_cmp_per_index_enabled
       </code>
      </a>
      system variable before performing the operations on compressed
          tables that you want to monitor.
     </p>
    </li>
    <li class="listitem">
     <p>
      For usage information, see
      <a class="xref" href="innodb-compression-tuning-monitoring.html" title="17.9.1.4 Monitoring InnoDB Table Compression at Runtime">
       Section 17.9.1.4, “Monitoring InnoDB Table Compression at Runtime”
      </a>
      and
      <a class="xref" href="innodb-information-schema-examples-compression-sect.html" title="17.15.1.3 Using the Compression Information Schema Tables">
       Section 17.15.1.3, “Using the Compression Information Schema Tables”
      </a>
      .
          For general information about
      <code class="literal">
       InnoDB
      </code>
      table
          compression, see
      <a class="xref" href="innodb-compression.html" title="17.9 InnoDB Table and Page Compression">
       Section 17.9, “InnoDB Table and Page Compression”
      </a>
      .
     </p>
    </li>
   </ul>
  </div>
 </div>
 <br/>
</div>
