<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h4 class="title">
      <a name="analyze-table">
      </a>
      15.7.3.1 ANALYZE TABLE Statement
     </h4>
    </div>
   </div>
  </div>
  <a class="indexterm" name="idm46045172710672">
  </a>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa65445979"><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">ANALYZE</span> <span class="token punctuation">[</span><span class="token keyword">NO_WRITE_TO_BINLOG</span> <span class="token operator">|</span> <span class="token keyword">LOCAL</span><span class="token punctuation">]</span>
    <span class="token keyword">TABLE</span> <em class="replaceable">tbl_name</em> <span class="token punctuation">[</span><span class="token punctuation">,</span> <em class="replaceable">tbl_name</em><span class="token punctuation">]</span> <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span>

<span class="token keyword">ANALYZE</span> <span class="token punctuation">[</span><span class="token keyword">NO_WRITE_TO_BINLOG</span> <span class="token operator">|</span> <span class="token keyword">LOCAL</span><span class="token punctuation">]</span>
    <span class="token keyword">TABLE</span> <em class="replaceable">tbl_name</em>
    <span class="token keyword">UPDATE</span> <span class="token keyword">HISTOGRAM</span> <span class="token keyword">ON</span> <em class="replaceable">col_name</em> <span class="token punctuation">[</span><span class="token punctuation">,</span> <em class="replaceable">col_name</em><span class="token punctuation">]</span> <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span>
        <span class="token punctuation">[</span><span class="token keyword">WITH</span> <em class="replaceable">N</em> <span class="token keyword">BUCKETS</span><span class="token punctuation">]</span>
    <span class="token punctuation">[</span>{MANUAL <span class="token operator">|</span> AUTO} <span class="token keyword">UPDATE</span><span class="token punctuation">]</span>

<span class="token keyword">ANALYZE</span> <span class="token punctuation">[</span><span class="token keyword">NO_WRITE_TO_BINLOG</span> <span class="token operator">|</span> <span class="token keyword">LOCAL</span><span class="token punctuation">]</span> 
    <span class="token keyword">TABLE</span> <em class="replaceable">tbl_name</em>
    <span class="token keyword">UPDATE</span> <span class="token keyword">HISTOGRAM</span> <span class="token keyword">ON</span> <em class="replaceable">col_name</em> <span class="token punctuation">[</span><span class="token keyword">USING</span> <span class="token keyword">DATA</span> <span class="token string">'<em class="replaceable">json_data</em>'</span><span class="token punctuation">]</span>

<span class="token keyword">ANALYZE</span> <span class="token punctuation">[</span><span class="token keyword">NO_WRITE_TO_BINLOG</span> <span class="token operator">|</span> <span class="token keyword">LOCAL</span><span class="token punctuation">]</span>
    <span class="token keyword">TABLE</span> <em class="replaceable">tbl_name</em>
    <span class="token keyword">DROP</span> <span class="token keyword">HISTOGRAM</span> <span class="token keyword">ON</span> <em class="replaceable">col_name</em> <span class="token punctuation">[</span><span class="token punctuation">,</span> <em class="replaceable">col_name</em><span class="token punctuation">]</span> <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span></code></pre>
  </div>
  <p>
   <a class="link" href="analyze-table.html" title="15.7.3.1 ANALYZE TABLE Statement">
    <code class="literal">
     ANALYZE TABLE
    </code>
   </a>
   generates table
        statistics:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      <a class="link" href="analyze-table.html" title="15.7.3.1 ANALYZE TABLE Statement">
       <code class="literal">
        ANALYZE TABLE
       </code>
      </a>
      without any
      <code class="literal">
       HISTOGRAM
      </code>
      clause performs a key
            distribution analysis and stores the distribution for the
            named table or tables. For
      <code class="literal">
       MyISAM
      </code>
      tables,
      <a class="link" href="analyze-table.html" title="15.7.3.1 ANALYZE TABLE Statement">
       <code class="literal">
        ANALYZE TABLE
       </code>
      </a>
      for key
            distribution analysis is equivalent to using
      <a class="link" href="myisamchk.html" title="6.6.4 myisamchk — MyISAM Table-Maintenance Utility">
       <span class="command">
        <strong>
         myisamchk --analyze
        </strong>
       </span>
      </a>
      .
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="link" href="analyze-table.html" title="15.7.3.1 ANALYZE TABLE Statement">
       <code class="literal">
        ANALYZE TABLE
       </code>
      </a>
      with the
      <code class="literal">
       UPDATE HISTOGRAM
      </code>
      clause generates
            histogram statistics for the named table columns and stores
            them in the data dictionary. Only one table name is
            permitted with this syntax. MySQL also supports setting the
            histogram of a single column to a user-defined JSON value.
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="link" href="analyze-table.html" title="15.7.3.1 ANALYZE TABLE Statement">
       <code class="literal">
        ANALYZE TABLE
       </code>
      </a>
      with the
      <code class="literal">
       DROP HISTOGRAM
      </code>
      clause removes histogram
            statistics for the named table columns from the data
            dictionary. Only one table name is permitted for this
            syntax.
     </p>
    </li>
   </ul>
  </div>
  <p>
   This statement requires
   <a class="link" href="privileges-provided.html#priv_select">
    <code class="literal">
     SELECT
    </code>
   </a>
   and
   <a class="link" href="privileges-provided.html#priv_insert">
    <code class="literal">
     INSERT
    </code>
   </a>
   privileges for the
        table.
  </p>
  <p>
   <a class="link" href="analyze-table.html" title="15.7.3.1 ANALYZE TABLE Statement">
    <code class="literal">
     ANALYZE TABLE
    </code>
   </a>
   works with
   <code class="literal">
    InnoDB
   </code>
   ,
   <code class="literal">
    NDB
   </code>
   , and
   <code class="literal">
    MyISAM
   </code>
   tables. It does not work with views.
  </p>
  <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>
  <p>
   <a class="link" href="analyze-table.html" title="15.7.3.1 ANALYZE TABLE Statement">
    <code class="literal">
     ANALYZE TABLE
    </code>
   </a>
   is supported for
        partitioned tables, and you can use
   <code class="literal">
    ALTER TABLE ...
        ANALYZE PARTITION
   </code>
   to analyze one or more partitions;
        for more information, see
   <a class="xref" href="alter-table.html" title="15.1.9 ALTER TABLE Statement">
    Section 15.1.9, “ALTER TABLE Statement”
   </a>
   , and
   <a class="xref" href="partitioning-maintenance.html" title="26.3.4 Maintenance of Partitions">
    Section 26.3.4, “Maintenance of Partitions”
   </a>
   .
  </p>
  <p>
   During the analysis, the table is locked with a read lock for
   <code class="literal">
    InnoDB
   </code>
   and
   <code class="literal">
    MyISAM
   </code>
   .
  </p>
  <p>
   By default, the server writes
   <a class="link" href="analyze-table.html" title="15.7.3.1 ANALYZE TABLE Statement">
    <code class="literal">
     ANALYZE
        TABLE
    </code>
   </a>
   statements to the binary log so that they
        replicate to replicas. To suppress logging, specify the optional
   <code class="literal">
    NO_WRITE_TO_BINLOG
   </code>
   keyword or its alias
   <code class="literal">
    LOCAL
   </code>
   .
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      <a class="xref" href="analyze-table.html#analyze-table-output" title="ANALYZE TABLE Output">
       ANALYZE TABLE Output
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="xref" href="analyze-table.html#analyze-table-key-distribution-analysis" title="Key Distribution Analysis">
       Key Distribution Analysis
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="xref" href="analyze-table.html#analyze-table-histogram-statistics-analysis" title="Histogram Statistics Analysis">
       Histogram Statistics Analysis
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="xref" href="analyze-table.html#analyze-table-other-considerations" title="Other Considerations">
       Other Considerations
      </a>
     </p>
    </li>
   </ul>
  </div>
  <div class="simplesect">
   <div class="titlepage">
    <div>
     <div class="simple">
      <h5 class="title">
       <a name="analyze-table-output">
       </a>
       ANALYZE TABLE Output
      </h5>
     </div>
    </div>
   </div>
   <p>
    <a class="link" href="analyze-table.html" title="15.7.3.1 ANALYZE TABLE Statement">
     <code class="literal">
      ANALYZE TABLE
     </code>
    </a>
    returns a result
          set with the columns shown in the following table.
   </p>
   <div class="informaltable">
    <table summary="Columns of the ANALYZE TABLE result set.">
     <colgroup>
      <col style="width: 15%"/>
      <col style="width: 60%"/>
     </colgroup>
     <thead>
      <tr>
       <th>
        Column
       </th>
       <th>
        Value
       </th>
      </tr>
     </thead>
     <tbody>
      <tr>
       <td>
        <code class="literal">
         Table
        </code>
       </td>
       <td>
        The table name
       </td>
      </tr>
      <tr>
       <td>
        <code class="literal">
         Op
        </code>
       </td>
       <td>
        <code class="literal">
         analyze
        </code>
        or
        <code class="literal">
         histogram
        </code>
       </td>
      </tr>
      <tr>
       <td>
        <code class="literal">
         Msg_type
        </code>
       </td>
       <td>
        <code class="literal">
         status
        </code>
        ,
        <code class="literal">
         error
        </code>
        ,
        <code class="literal">
         info
        </code>
        ,
        <code class="literal">
         note
        </code>
        , or
        <code class="literal">
         warning
        </code>
       </td>
      </tr>
      <tr>
       <td>
        <code class="literal">
         Msg_text
        </code>
       </td>
       <td>
        An informational message
       </td>
      </tr>
     </tbody>
    </table>
   </div>
  </div>
  <div class="simplesect">
   <div class="titlepage">
    <div>
     <div class="simple">
      <h5 class="title">
       <a name="analyze-table-key-distribution-analysis">
       </a>
       Key Distribution Analysis
      </h5>
     </div>
    </div>
   </div>
   <p>
    <a class="link" href="analyze-table.html" title="15.7.3.1 ANALYZE TABLE Statement">
     <code class="literal">
      ANALYZE TABLE
     </code>
    </a>
    without either
    <code class="literal">
     HISTOGRAM
    </code>
    clause performs a key
          distribution analysis and stores the distribution for the
          table or tables. Any existing histogram statistics remain
          unaffected.
   </p>
   <p>
    If the table has not changed since the last key distribution
          analysis, the table is not analyzed again.
   </p>
   <p>
    MySQL uses the stored key distribution to decide the order in
          which tables should be joined for joins on something other
          than a constant. In addition, key distributions can be used
          when deciding which indexes to use for a specific table within
          a query.
   </p>
   <p>
    To check the stored key distribution cardinality, use the
    <a class="link" href="show-index.html" title="15.7.7.23 SHOW INDEX Statement">
     <code class="literal">
      SHOW INDEX
     </code>
    </a>
    statement or 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="show-index.html" title="15.7.7.23 SHOW INDEX Statement">
     Section 15.7.7.23, “SHOW INDEX Statement”
    </a>
    , and
    <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>
    .
   </p>
   <p>
    For
    <code class="literal">
     InnoDB
    </code>
    tables,
    <a class="link" href="analyze-table.html" title="15.7.3.1 ANALYZE TABLE Statement">
     <code class="literal">
      ANALYZE TABLE
     </code>
    </a>
    determines index
          cardinality by performing random dives on each of the index
          trees and updating index cardinality estimates accordingly.
          Because these are only estimates, repeated runs of
    <a class="link" href="analyze-table.html" title="15.7.3.1 ANALYZE TABLE Statement">
     <code class="literal">
      ANALYZE TABLE
     </code>
    </a>
    could produce
          different numbers. This makes
    <a class="link" href="analyze-table.html" title="15.7.3.1 ANALYZE TABLE Statement">
     <code class="literal">
      ANALYZE
          TABLE
     </code>
    </a>
    fast on
    <code class="literal">
     InnoDB
    </code>
    tables but
          not 100% accurate because it does not take all rows into
          account.
   </p>
   <p>
    You can make the
    <a class="link" href="glossary.html#glos_statistics" title="statistics">
     statistics
    </a>
    collected by
    <a class="link" href="analyze-table.html" title="15.7.3.1 ANALYZE TABLE Statement">
     <code class="literal">
      ANALYZE TABLE
     </code>
    </a>
    more precise and
          more stable by enabling
    <a class="link" href="innodb-parameters.html#sysvar_innodb_stats_persistent">
     <code class="literal">
      innodb_stats_persistent
     </code>
    </a>
    , as
          explained in
    <a class="xref" href="innodb-persistent-stats.html" title="17.8.10.1 Configuring Persistent Optimizer Statistics Parameters">
     Section 17.8.10.1, “Configuring Persistent Optimizer Statistics Parameters”
    </a>
    . When
    <a class="link" href="innodb-parameters.html#sysvar_innodb_stats_persistent">
     <code class="literal">
      innodb_stats_persistent
     </code>
    </a>
    is
          enabled, it is important to run
    <a class="link" href="analyze-table.html" title="15.7.3.1 ANALYZE TABLE Statement">
     <code class="literal">
      ANALYZE
          TABLE
     </code>
    </a>
    after major changes to index column data, as
          statistics are not recalculated periodically (such as after a
          server restart).
   </p>
   <p>
    If
    <a class="link" href="innodb-parameters.html#sysvar_innodb_stats_persistent">
     <code class="literal">
      innodb_stats_persistent
     </code>
    </a>
    is
          enabled, you can change the number of random dives by
          modifying the
    <a class="link" href="innodb-parameters.html#sysvar_innodb_stats_persistent_sample_pages">
     <code class="literal">
      innodb_stats_persistent_sample_pages
     </code>
    </a>
    system variable. If
    <a class="link" href="innodb-parameters.html#sysvar_innodb_stats_persistent">
     <code class="literal">
      innodb_stats_persistent
     </code>
    </a>
    is
          disabled, modify
    <a class="link" href="innodb-parameters.html#sysvar_innodb_stats_transient_sample_pages">
     <code class="literal">
      innodb_stats_transient_sample_pages
     </code>
    </a>
    instead.
   </p>
   <p>
    For more information about key distribution analysis in
    <code class="literal">
     InnoDB
    </code>
    , see
    <a class="xref" href="innodb-persistent-stats.html" title="17.8.10.1 Configuring Persistent Optimizer Statistics Parameters">
     Section 17.8.10.1, “Configuring Persistent Optimizer Statistics Parameters”
    </a>
    , and
    <a class="xref" href="innodb-analyze-table-complexity.html" title="17.8.10.3 Estimating ANALYZE TABLE Complexity for InnoDB Tables">
     Section 17.8.10.3, “Estimating ANALYZE TABLE Complexity for InnoDB Tables”
    </a>
    .
   </p>
   <p>
    MySQL uses index cardinality estimates in join optimization.
          If a join is not optimized in the right way, try running
    <a class="link" href="analyze-table.html" title="15.7.3.1 ANALYZE TABLE Statement">
     <code class="literal">
      ANALYZE TABLE
     </code>
    </a>
    . In the few cases
          that
    <a class="link" href="analyze-table.html" title="15.7.3.1 ANALYZE TABLE Statement">
     <code class="literal">
      ANALYZE TABLE
     </code>
    </a>
    does not
          produce values good enough for your particular tables, you can
          use
    <code class="literal">
     FORCE INDEX
    </code>
    with your queries to force
          the use of a particular index, or set the
    <a class="link" href="server-system-variables.html#sysvar_max_seeks_for_key">
     <code class="literal">
      max_seeks_for_key
     </code>
    </a>
    system
          variable to ensure that MySQL prefers index lookups over table
          scans. See
    <a class="xref" href="optimizer-issues.html" title="B.3.5 Optimizer-Related Issues">
     Section B.3.5, “Optimizer-Related Issues”
    </a>
    .
   </p>
  </div>
  <div class="simplesect">
   <div class="titlepage">
    <div>
     <div class="simple">
      <h5 class="title">
       <a name="analyze-table-histogram-statistics-analysis">
       </a>
       Histogram Statistics Analysis
      </h5>
     </div>
    </div>
   </div>
   <p>
    <a class="link" href="analyze-table.html" title="15.7.3.1 ANALYZE TABLE Statement">
     <code class="literal">
      ANALYZE TABLE
     </code>
    </a>
    with the
    <code class="literal">
     HISTOGRAM
    </code>
    clause enables management of
          histogram statistics for table column values. For information
          about histogram statistics, see
    <a class="xref" href="optimizer-statistics.html" title="10.9.6 Optimizer Statistics">
     Section 10.9.6, “Optimizer Statistics”
    </a>
    .
   </p>
   <p>
    These histogram operations are available:
   </p>
   <div class="itemizedlist">
    <ul class="itemizedlist" style="list-style-type: disc; ">
     <li class="listitem">
      <p>
       <a class="link" href="analyze-table.html" title="15.7.3.1 ANALYZE TABLE Statement">
        <code class="literal">
         ANALYZE TABLE
        </code>
       </a>
       with an
       <code class="literal">
        UPDATE HISTOGRAM
       </code>
       clause generates
              histogram statistics for the named table columns and
              stores them in the data dictionary. Only one table name is
              permitted for this syntax.
      </p>
      <p>
       The optional
       <code class="literal">
        WITH
        <em class="replaceable">
         <code>
          N
         </code>
        </em>
        BUCKETS
       </code>
       clause specifies the number of buckets
              for the histogram. The value of
       <em class="replaceable">
        <code>
         N
        </code>
       </em>
       must be an integer in the
              range from 1 to 1024. If this clause is omitted, the
              number of buckets is 100.
      </p>
      <p>
       The optional
       <code class="literal">
        AUTO UPDATE
       </code>
       clause enables
              automatic updates of histograms on the table. When
              enabled, an
       <code class="literal">
        ANALYZE TABLE
       </code>
       statement on
              this table automatically updates the histogram, using the
              same number of buckets as last specified by
       <code class="literal">
        WITH
              ... BUCKETS
       </code>
       if this was previously set for this
              table. In addition, when recalculating persistent
              statistics for the table (see
       <a class="xref" href="innodb-persistent-stats.html" title="17.8.10.1 Configuring Persistent Optimizer Statistics Parameters">
        Section 17.8.10.1, “Configuring Persistent Optimizer Statistics Parameters”
       </a>
       ), the
       <a class="link" href="innodb-storage-engine.html" title="Chapter 17 The InnoDB Storage Engine">
        <code class="literal">
         InnoDB
        </code>
       </a>
       background statistics
              thread also updates the histogram.
       <code class="literal">
        MANUAL
              UPDATE
       </code>
       disables automatic updates, and is the
              default setting if not specified.
      </p>
     </li>
     <li class="listitem">
      <p>
       <a class="link" href="analyze-table.html" title="15.7.3.1 ANALYZE TABLE Statement">
        <code class="literal">
         ANALYZE TABLE
        </code>
       </a>
       with a
       <code class="literal">
        DROP HISTOGRAM
       </code>
       clause removes histogram
              statistics for the named table columns from the data
              dictionary. Only one table name is permitted for this
              syntax.
      </p>
     </li>
    </ul>
   </div>
   <p>
    Stored histogram management statements affect only the named
          columns. Consider these statements:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa1154591"><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">ANALYZE</span> <span class="token keyword">TABLE</span> t <span class="token keyword">UPDATE</span> <span class="token keyword">HISTOGRAM</span> <span class="token keyword">ON</span> c1<span class="token punctuation">,</span> c2<span class="token punctuation">,</span> c3 <span class="token keyword">WITH</span> <span class="token number">10</span> <span class="token keyword">BUCKETS</span><span class="token punctuation">;</span>
<span class="token keyword">ANALYZE</span> <span class="token keyword">TABLE</span> t <span class="token keyword">UPDATE</span> <span class="token keyword">HISTOGRAM</span> <span class="token keyword">ON</span> c1<span class="token punctuation">,</span> c3 <span class="token keyword">WITH</span> <span class="token number">10</span> <span class="token keyword">BUCKETS</span><span class="token punctuation">;</span>
<span class="token keyword">ANALYZE</span> <span class="token keyword">TABLE</span> t <span class="token keyword">DROP</span> <span class="token keyword">HISTOGRAM</span> <span class="token keyword">ON</span> c2<span class="token punctuation">;</span></code></pre>
   </div>
   <p>
    The first statement updates the histograms for columns
    <code class="literal">
     c1
    </code>
    ,
    <code class="literal">
     c2
    </code>
    , and
    <code class="literal">
     c3
    </code>
    , replacing any existing histograms for
          those columns. The second statement updates the histograms for
    <code class="literal">
     c1
    </code>
    and
    <code class="literal">
     c3
    </code>
    , leaving the
    <code class="literal">
     c2
    </code>
    histogram unaffected. The third
          statement removes the histogram for
    <code class="literal">
     c2
    </code>
    ,
          leaving those for
    <code class="literal">
     c1
    </code>
    and
    <code class="literal">
     c3
    </code>
    unaffected.
   </p>
   <p>
    When sampling user data as part of building a histogram, not
          all values are read; this may lead to missing some values
          considered important. In such cases, it might be useful to
          modify the histogram, or to set your own histogram explicitly
          based on your own criteria, such as the complete data set.
    <code class="literal">
     ANALYZE TABLE
     <em class="replaceable">
      <code>
       tbl_name
      </code>
     </em>
     UPDATE HISTOGRAM ON
     <em class="replaceable">
      <code>
       col_name
      </code>
     </em>
     USING
          DATA '
     <em class="replaceable">
      <code>
       json_data
      </code>
     </em>
     '
    </code>
    updates
          a column of the histogram table with data supplied in the same
          JSON format used to display
    <code class="literal">
     HISTOGRAM
    </code>
    column values from the Information Schema
    <a class="link" href="information-schema-column-statistics-table.html" title="28.3.11 The INFORMATION_SCHEMA COLUMN_STATISTICS Table">
     <code class="literal">
      COLUMN_STATISTICS
     </code>
    </a>
    table. Only one
          column can be modified when updating the histogram with JSON
          data.
   </p>
   <p>
    We can illustrate the use of
    <code class="literal">
     USING DATA
    </code>
    by
          first generating a histogram on column
    <code class="literal">
     c1
    </code>
    of table
    <code class="literal">
     t
    </code>
    , like this:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa74049284"><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">ANALYZE</span> <span class="token keyword">TABLE</span> t <span class="token keyword">UPDATE</span> <span class="token keyword">HISTOGRAM</span> <span class="token keyword">ON</span> c1<span class="token punctuation">;</span>
<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><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span> Table <span class="token punctuation">|</span> Op        <span class="token punctuation">|</span> Msg_type <span class="token punctuation">|</span> Msg_text                                      <span class="token punctuation">|</span></span>
<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><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span> h.t   <span class="token punctuation">|</span> histogram <span class="token punctuation">|</span> status   <span class="token punctuation">|</span> Histogram statistics created for column 'c1'. <span class="token punctuation">|</span></span>
<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><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output">1 row in set (0.00 sec)</span></code></pre>
   </div>
   <p>
    We can see the histogram generated in the
    <code class="literal">
     COLUMN_STATISTICS
    </code>
    table:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa2514908"><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">TABLE</span> information_schema<span class="token punctuation">.</span>column_statistics\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>
SCHEMA_NAME<span class="token punctuation">:</span> h
 TABLE_NAME<span class="token punctuation">:</span> t
COLUMN_NAME<span class="token punctuation">:</span> c1
  HISTOGRAM<span class="token punctuation">:</span> {"buckets"<span class="token punctuation">:</span> [], "data-type"<span class="token punctuation">:</span> "int", "auto-update"<span class="token punctuation">:</span> false,
"null-values"<span class="token punctuation">:</span> 0.0, "collation-id"<span class="token punctuation">:</span> 8, "last-updated"<span class="token punctuation">:</span> "2024-03-26
16<span class="token punctuation">:</span>54<span class="token punctuation">:</span>43.674995", "sampling-rate"<span class="token punctuation">:</span> 1.0, "histogram-type"<span class="token punctuation">:</span> "singleton",
"number-of-buckets-specified"<span class="token punctuation">:</span> 100}   
</span><span class="token output">1 row in set (0.00 sec)</span></code></pre>
   </div>
   <p>
    Now we drop the histogram, and when we check
    <code class="literal">
     COLUMN_STATISTICS
    </code>
    , it is empty:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa62507846"><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">ANALYZE</span> <span class="token keyword">TABLE</span> t <span class="token keyword">DROP</span> <span class="token keyword">HISTOGRAM</span> <span class="token keyword">ON</span> c1<span class="token punctuation">;</span>
<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><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span> Table <span class="token punctuation">|</span> Op        <span class="token punctuation">|</span> Msg_type <span class="token punctuation">|</span> Msg_text                                      <span class="token punctuation">|</span></span>
<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><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span> h.t   <span class="token punctuation">|</span> histogram <span class="token punctuation">|</span> status   <span class="token punctuation">|</span> Histogram statistics removed for column 'c1'. <span class="token punctuation">|</span></span>
<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><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output">1 row in set (0.01 sec)</span>

<span class="token prompt">mysql&gt;</span> <span class="token keyword">TABLE</span> information_schema<span class="token punctuation">.</span>column_statistics\G
<span class="token output">Empty set (0.00 sec)</span></code></pre>
   </div>
   <p>
    We can restore the dropped histogram by inserting its JSON
          representation obtained previously from the
    <code class="literal">
     HISTOGRAM
    </code>
    column of the
    <code class="literal">
     COLUMN_STATISTICS
    </code>
    table, and when we query
          that table again, we can see that the histogram has been
          restored to its previous state:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa6553885"><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">ANALYZE</span> <span class="token keyword">TABLE</span> t <span class="token keyword">UPDATE</span> <span class="token keyword">HISTOGRAM</span> <span class="token keyword">ON</span> c1 
<span class="token prompt">    -&gt;</span>     <span class="token keyword">USING</span> <span class="token keyword">DATA</span> <span class="token string">'{"buckets": [], "data-type": "int", "auto-update": false,
    -&gt;               "null-values": 0.0, "collation-id": 8, "last-updated": "2024-03-26
    -&gt;               16:54:43.674995", "sampling-rate": 1.0, "histogram-type": "singleton",
    -&gt;               "number-of-buckets-specified": 100}'</span><span class="token punctuation">;</span>   
<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><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span> Table <span class="token punctuation">|</span> Op        <span class="token punctuation">|</span> Msg_type <span class="token punctuation">|</span> Msg_text                                      <span class="token punctuation">|</span></span>
<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><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span> h.t   <span class="token punctuation">|</span> histogram <span class="token punctuation">|</span> status   <span class="token punctuation">|</span> Histogram statistics created for column 'c1'. <span class="token punctuation">|</span></span>
<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><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>

<span class="token prompt">mysql&gt;</span> <span class="token keyword">TABLE</span> information_schema<span class="token punctuation">.</span>column_statistics\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>
SCHEMA_NAME<span class="token punctuation">:</span> h
 TABLE_NAME<span class="token punctuation">:</span> t
COLUMN_NAME<span class="token punctuation">:</span> c1
  HISTOGRAM<span class="token punctuation">:</span> {"buckets"<span class="token punctuation">:</span> [], "data-type"<span class="token punctuation">:</span> "int", "auto-update"<span class="token punctuation">:</span> false,
"null-values"<span class="token punctuation">:</span> 0.0, "collation-id"<span class="token punctuation">:</span> 8, "last-updated"<span class="token punctuation">:</span> "2024-03-26
16<span class="token punctuation">:</span>54<span class="token punctuation">:</span>43.674995", "sampling-rate"<span class="token punctuation">:</span> 1.0, "histogram-type"<span class="token punctuation">:</span> "singleton",
"number-of-buckets-specified"<span class="token punctuation">:</span> 100}</span></code></pre>
   </div>
   <p>
    Histogram generation is not supported for encrypted tables (to
          avoid exposing data in the statistics) or
    <code class="literal">
     TEMPORARY
    </code>
    tables.
   </p>
   <p>
    Histogram generation applies to columns of all data types
          except geometry types (spatial data) and
    <a class="link" href="json.html" title="13.5 The JSON Data Type">
     <code class="literal">
      JSON
     </code>
    </a>
    .
   </p>
   <p>
    Histograms can be generated for stored and virtual generated
          columns.
   </p>
   <p>
    Histograms cannot be generated for columns that are covered by
          single-column unique indexes.
   </p>
   <p>
    Histogram management statements attempt to perform as much of
          the requested operation as possible, and report diagnostic
          messages for the remainder. For example, if an
    <code class="literal">
     UPDATE
          HISTOGRAM
    </code>
    statement names multiple columns, but some
          of them do not exist or have an unsupported data type,
          histograms are generated for the other columns, and messages
          are produced for the invalid columns.
   </p>
   <p>
    Histograms are affected by these DDL statements:
   </p>
   <div class="itemizedlist">
    <ul class="itemizedlist" style="list-style-type: disc; ">
     <li class="listitem">
      <p>
       <a class="link" href="drop-table.html" title="15.1.32 DROP TABLE Statement">
        <code class="literal">
         DROP TABLE
        </code>
       </a>
       removes
              histograms for columns in the dropped table.
      </p>
     </li>
     <li class="listitem">
      <p>
       <a class="link" href="drop-database.html" title="15.1.24 DROP DATABASE Statement">
        <code class="literal">
         DROP DATABASE
        </code>
       </a>
       removes
              histograms for any table in the dropped database because
              the statement drops all tables in the database.
      </p>
     </li>
     <li class="listitem">
      <p>
       <a class="link" href="rename-table.html" title="15.1.36 RENAME TABLE Statement">
        <code class="literal">
         RENAME TABLE
        </code>
       </a>
       does not
              remove histograms. Instead, it renames histograms for the
              renamed table to be associated with the new table name.
      </p>
     </li>
     <li class="listitem">
      <p>
       <a class="link" href="alter-table.html" title="15.1.9 ALTER TABLE Statement">
        <code class="literal">
         ALTER TABLE
        </code>
       </a>
       statements that
              remove or modify a column remove histograms for that
              column.
      </p>
     </li>
     <li class="listitem">
      <p>
       <a class="link" href="alter-table.html" title="15.1.9 ALTER TABLE Statement">
        <code class="literal">
         ALTER TABLE
              ... CONVERT TO CHARACTER SET
        </code>
       </a>
       removes histograms
              for character columns because they are affected by the
              change of character set. Histograms for noncharacter
              columns remain unaffected.
      </p>
     </li>
    </ul>
   </div>
   <p>
    The
    <a class="link" href="server-system-variables.html#sysvar_histogram_generation_max_mem_size">
     <code class="literal">
      histogram_generation_max_mem_size
     </code>
    </a>
    system variable controls the maximum amount of memory
          available for histogram generation. The global and session
          values may be set at runtime.
   </p>
   <p>
    Changing the global
    <a class="link" href="server-system-variables.html#sysvar_histogram_generation_max_mem_size">
     <code class="literal">
      histogram_generation_max_mem_size
     </code>
    </a>
    value requires privileges sufficient to set global system
          variables. Changing the session
    <a class="link" href="server-system-variables.html#sysvar_histogram_generation_max_mem_size">
     <code class="literal">
      histogram_generation_max_mem_size
     </code>
    </a>
    value requires privileges sufficient to set restricted session
          system variables. See
    <a class="xref" href="system-variable-privileges.html" title="7.1.9.1 System Variable Privileges">
     Section 7.1.9.1, “System Variable Privileges”
    </a>
    .
   </p>
   <p>
    If the estimated amount of data to be read into memory for
          histogram generation exceeds the limit defined by
    <a class="link" href="server-system-variables.html#sysvar_histogram_generation_max_mem_size">
     <code class="literal">
      histogram_generation_max_mem_size
     </code>
    </a>
    ,
          MySQL samples the data rather than reading all of it into
          memory. Sampling is evenly distributed over the entire table.
          MySQL uses
    <code class="literal">
     SYSTEM
    </code>
    sampling, which is a
          page-level sampling method.
   </p>
   <p>
    The
    <code class="literal">
     sampling-rate
    </code>
    value in the
    <code class="literal">
     HISTOGRAM
    </code>
    column of the Information Schema
    <a class="link" href="information-schema-column-statistics-table.html" title="28.3.11 The INFORMATION_SCHEMA COLUMN_STATISTICS Table">
     <code class="literal">
      COLUMN_STATISTICS
     </code>
    </a>
    table can be
          queried to determine the fraction of data that was sampled to
          create the histogram. The
    <code class="literal">
     sampling-rate
    </code>
    is
          a number between 0.0 and 1.0. A value of 1 means that all of
          the data was read (no sampling).
   </p>
   <p>
    The following example demonstrates sampling. To ensure that
          the amount of data exceeds the
    <a class="link" href="server-system-variables.html#sysvar_histogram_generation_max_mem_size">
     <code class="literal">
      histogram_generation_max_mem_size
     </code>
    </a>
    limit for the purpose of the example, the limit is set to a
          low value (2000000 bytes) prior to generating histogram
          statistics for the
    <code class="literal">
     birth_date
    </code>
    column of the
    <code class="literal">
     employees
    </code>
    table.
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa43880711"><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">SET</span> histogram_generation_max_mem_size <span class="token operator">=</span> <span class="token number">2000000</span><span class="token punctuation">;</span>

<span class="token prompt">mysql&gt;</span> <span class="token keyword">USE</span> employees<span class="token punctuation">;</span>

<span class="token prompt">mysql&gt;</span> <span class="token keyword">ANALYZE</span> <span class="token keyword">TABLE</span> employees <span class="token keyword">UPDATE</span> <span class="token keyword">HISTOGRAM</span> <span class="token keyword">ON</span> birth_date <span class="token keyword">WITH</span> <span class="token number">16</span> <span class="token keyword">BUCKETS</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<span class="token punctuation">:</span> employees.employees
      Op<span class="token punctuation">:</span> histogram
Msg_type<span class="token punctuation">:</span> status
Msg_text<span class="token punctuation">:</span> Histogram statistics created for column 'birth_date'.
</span>
<span class="token prompt">mysql&gt;</span> <span class="token keyword">SELECT</span> <span class="token keyword">HISTOGRAM</span><span class="token operator">-</span><span class="token operator">&gt;&gt;</span><span class="token string">'$."sampling-rate"'</span>
       <span class="token keyword">FROM</span> INFORMATION_SCHEMA<span class="token punctuation">.</span>COLUMN_STATISTICS
       <span class="token keyword">WHERE</span> <span class="token keyword">TABLE_NAME</span> <span class="token operator">=</span> <span class="token string">"employees"</span>
       <span class="token operator">AND</span> <span class="token keyword">COLUMN_NAME</span> <span class="token operator">=</span> <span class="token string">"birth_date"</span><span class="token punctuation">;</span>
<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><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span> HISTOGRAM<span class="token punctuation">-</span>&gt;&gt;'$."sampling<span class="token punctuation">-</span>rate"' <span class="token punctuation">|</span></span>
<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><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span> 0.0491431208869665              <span class="token punctuation">|</span></span>
<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><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span></code></pre>
   </div>
   <p>
    A
    <code class="literal">
     sampling-rate
    </code>
    value of 0.0491431208869665
          means that approximately 4.9% of the data from the
    <code class="literal">
     birth_date
    </code>
    column was read into memory for
          generating histogram statistics.
   </p>
   <p>
    The
    <a class="link" href="innodb-storage-engine.html" title="Chapter 17 The InnoDB Storage Engine">
     <code class="literal">
      InnoDB
     </code>
    </a>
    storage engine
          provides its own sampling implementation for data stored in
    <code class="literal">
     InnoDB
    </code>
    tables. The default sampling
          implementation used by MySQL when storage engines do not
          provide their own requires a full table scan, which is costly
          for large tables. The
    <code class="literal">
     InnoDB
    </code>
    sampling
          implementation improves sampling performance by avoiding full
          table scans.
   </p>
   <p>
    The
    <code class="literal">
     sampled_pages_read
    </code>
    and
    <code class="literal">
     sampled_pages_skipped
    </code>
    <a class="link" href="information-schema-innodb-metrics-table.html" title="28.4.21 The INFORMATION_SCHEMA INNODB_METRICS Table">
     <code class="literal">
      INNODB_METRICS
     </code>
    </a>
    counters can be
          used to monitor sampling of
    <code class="literal">
     InnoDB
    </code>
    data
          pages. (For general
    <a class="link" href="information-schema-innodb-metrics-table.html" title="28.4.21 The INFORMATION_SCHEMA INNODB_METRICS Table">
     <code class="literal">
      INNODB_METRICS
     </code>
    </a>
    counter usage
          information, see
    <a class="xref" href="information-schema-innodb-metrics-table.html" title="28.4.21 The INFORMATION_SCHEMA INNODB_METRICS Table">
     Section 28.4.21, “The INFORMATION_SCHEMA INNODB_METRICS Table”
    </a>
    .)
   </p>
   <p>
    The following example demonstrates sampling counter usage,
          which requires enabling the counters prior to generating
          histogram statistics.
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa27536275"><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">SET</span> <span class="token keyword">GLOBAL</span> innodb_monitor_enable <span class="token operator">=</span> <span class="token string">'sampled%'</span><span class="token punctuation">;</span>

<span class="token prompt">mysql&gt;</span> <span class="token keyword">USE</span> employees<span class="token punctuation">;</span>

<span class="token prompt">mysql&gt;</span> <span class="token keyword">ANALYZE</span> <span class="token keyword">TABLE</span> employees <span class="token keyword">UPDATE</span> <span class="token keyword">HISTOGRAM</span> <span class="token keyword">ON</span> birth_date <span class="token keyword">WITH</span> <span class="token number">16</span> <span class="token keyword">BUCKETS</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<span class="token punctuation">:</span> employees.employees
      Op<span class="token punctuation">:</span> histogram
Msg_type<span class="token punctuation">:</span> status
Msg_text<span class="token punctuation">:</span> Histogram statistics created for column 'birth_date'.
</span>
<span class="token prompt">mysql&gt;</span> <span class="token keyword">USE</span> INFORMATION_SCHEMA<span class="token punctuation">;</span>

<span class="token prompt">mysql&gt;</span> <span class="token keyword">SELECT</span> <span class="token keyword">NAME</span><span class="token punctuation">,</span> COUNT <span class="token keyword">FROM</span> INNODB_METRICS <span class="token keyword">WHERE</span> <span class="token keyword">NAME</span> <span class="token operator">LIKE</span> <span class="token string">'sampled%'</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>
 NAME<span class="token punctuation">:</span> sampled_pages_read
COUNT<span class="token punctuation">:</span> 43
<span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span 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>
 NAME<span class="token punctuation">:</span> sampled_pages_skipped
COUNT<span class="token punctuation">:</span> 843</span></code></pre>
   </div>
   <p>
    This formula approximates a sampling rate based on the
          sampling counter data:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting one-line language-none"><div class="docs-select-all right" id="sa65336282"><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-none">sampling rate = sampled_page_read/(sampled_pages_read + sampled_pages_skipped)</code></pre>
   </div>
   <p>
    A sampling rate based on sampling counter data is roughly the
          same as the
    <code class="literal">
     sampling-rate
    </code>
    value in the
    <code class="literal">
     HISTOGRAM
    </code>
    column of the Information Schema
    <a class="link" href="information-schema-column-statistics-table.html" title="28.3.11 The INFORMATION_SCHEMA COLUMN_STATISTICS Table">
     <code class="literal">
      COLUMN_STATISTICS
     </code>
    </a>
    table.
   </p>
   <p>
    For information about memory allocations performed for
          histogram generation, monitor the Performance Schema
    <code class="literal">
     memory/sql/histograms
    </code>
    instrument. See
    <a class="xref" href="performance-schema-memory-summary-tables.html" title="29.12.20.10 Memory Summary Tables">
     Section 29.12.20.10, “Memory Summary Tables”
    </a>
    .
   </p>
  </div>
  <div class="simplesect">
   <div class="titlepage">
    <div>
     <div class="simple">
      <h5 class="title">
       <a name="analyze-table-other-considerations">
       </a>
       Other Considerations
      </h5>
     </div>
    </div>
   </div>
   <p>
    <code class="literal">
     ANALYZE TABLE
    </code>
    clears table statistics from
          the Information Schema
    <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 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>
  </div>
 </div>
 <br/>
</div>
