<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h4 class="title">
      <a name="innodb-analyze-table-complexity">
      </a>
      17.8.10.3 Estimating ANALYZE TABLE Complexity for InnoDB Tables
     </h4>
    </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>
   complexity for
   <code class="literal">
    InnoDB
   </code>
   tables is dependent on:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      The number of pages sampled, as defined by
      <a class="link" href="innodb-parameters.html#sysvar_innodb_stats_persistent_sample_pages">
       <code class="literal">
        innodb_stats_persistent_sample_pages
       </code>
      </a>
      .
     </p>
    </li>
    <li class="listitem">
     <p>
      The number of indexed columns in a table
     </p>
    </li>
    <li class="listitem">
     <p>
      The number of partitions. If a table has no partitions, the
            number of partitions is considered to be 1.
     </p>
    </li>
   </ul>
  </div>
  <p>
   Using these parameters, an approximate formula for estimating
   <a class="link" href="analyze-table.html" title="15.7.3.1 ANALYZE TABLE Statement">
    <code class="literal">
     ANALYZE TABLE
    </code>
   </a>
   complexity would
        be:
  </p>
  <p>
   The value of
   <a class="link" href="innodb-parameters.html#sysvar_innodb_stats_persistent_sample_pages">
    <code class="literal">
     innodb_stats_persistent_sample_pages
    </code>
   </a>
   * number of indexed columns in a table * the number of
        partitions
  </p>
  <p>
   Typically, the greater the resulting value, the greater the
        execution time for
   <a class="link" href="analyze-table.html" title="15.7.3.1 ANALYZE TABLE Statement">
    <code class="literal">
     ANALYZE TABLE
    </code>
   </a>
   .
  </p>
  <div class="note" style="margin-left: 0.5in; margin-right: 0.5in;">
   <div class="admon-title">
    Note
   </div>
   <p>
    <a class="link" href="innodb-parameters.html#sysvar_innodb_stats_persistent_sample_pages">
     <code class="literal">
      innodb_stats_persistent_sample_pages
     </code>
    </a>
    defines the number of pages sampled at a global level. To set
          the number of pages sampled for an individual table, use the
    <code class="literal">
     STATS_SAMPLE_PAGES
    </code>
    option with
    <a class="link" href="create-table.html" title="15.1.20 CREATE TABLE Statement">
     <code class="literal">
      CREATE TABLE
     </code>
    </a>
    or
    <a class="link" href="alter-table.html" title="15.1.9 ALTER TABLE Statement">
     <code class="literal">
      ALTER TABLE
     </code>
    </a>
    . For more
          information, 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>
    .
   </p>
   <p>
    If
    <a class="link" href="innodb-parameters.html#sysvar_innodb_stats_persistent">
     <code class="literal">
      innodb_stats_persistent=OFF
     </code>
    </a>
    ,
          the number of pages sampled is defined by
    <a class="link" href="innodb-parameters.html#sysvar_innodb_stats_transient_sample_pages">
     <code class="literal">
      innodb_stats_transient_sample_pages
     </code>
    </a>
    .
          See
    <a class="xref" href="innodb-statistics-estimation.html" title="17.8.10.2 Configuring Non-Persistent Optimizer Statistics Parameters">
     Section 17.8.10.2, “Configuring Non-Persistent Optimizer Statistics Parameters”
    </a>
    for
          additional information.
   </p>
  </div>
  <p>
   For a more in-depth approach to estimating
   <code class="literal">
    ANALYZE
        TABLE
   </code>
   complexity, consider the following example.
  </p>
  <p>
   In
   <a class="ulink" href="http://en.wikipedia.org/wiki/Big_O_notation" target="_blank">
    Big
        O notation
   </a>
   ,
   <a class="link" href="analyze-table.html" title="15.7.3.1 ANALYZE TABLE Statement">
    <code class="literal">
     ANALYZE TABLE
    </code>
   </a>
   complexity is described as:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-none"><div class="docs-select-all right" id="sa23785459"><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"> O(n_sample
  * (n_cols_in_uniq_i
     + n_cols_in_non_uniq_i
     + n_cols_in_pk * (1 + n_non_uniq_i))
  * n_part)</code></pre>
  </div>
  <p>
   where:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      <code class="literal">
       n_sample
      </code>
      is the number of pages sampled
            (defined by
      <a class="link" href="innodb-parameters.html#sysvar_innodb_stats_persistent_sample_pages">
       <code class="literal">
        innodb_stats_persistent_sample_pages
       </code>
      </a>
      )
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       n_cols_in_uniq_i
      </code>
      is total number of all
            columns in all unique indexes (not counting the primary key
            columns)
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       n_cols_in_non_uniq_i
      </code>
      is the total number
            of all columns in all nonunique indexes
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       n_cols_in_pk
      </code>
      is the number of columns in
            the primary key (if a primary key is not defined,
      <code class="literal">
       InnoDB
      </code>
      creates a single column primary
            key internally)
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       n_non_uniq_i
      </code>
      is the number of nonunique
            indexes in the table
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       n_part
      </code>
      is the number of partitions. If no
            partitions are defined, the table is considered to be a
            single partition.
     </p>
    </li>
   </ul>
  </div>
  <p>
   Now, consider the following table (table
   <code class="literal">
    t
   </code>
   ),
        which has a primary key (2 columns), a unique index (2 columns),
        and two nonunique indexes (two columns each):
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa84666814"><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">CREATE</span> <span class="token keyword">TABLE</span> t <span class="token punctuation">(</span>
  a <span class="token datatype">INT</span><span class="token punctuation">,</span>
  b <span class="token datatype">INT</span><span class="token punctuation">,</span>
  c <span class="token datatype">INT</span><span class="token punctuation">,</span>
  d <span class="token datatype">INT</span><span class="token punctuation">,</span>
  e <span class="token datatype">INT</span><span class="token punctuation">,</span>
  f <span class="token datatype">INT</span><span class="token punctuation">,</span>
  g <span class="token datatype">INT</span><span class="token punctuation">,</span>
  h <span class="token datatype">INT</span><span class="token punctuation">,</span>
  <span class="token keyword">PRIMARY</span> <span class="token keyword">KEY</span> <span class="token punctuation">(</span>a<span class="token punctuation">,</span> b<span class="token punctuation">)</span><span class="token punctuation">,</span>
  <span class="token keyword">UNIQUE</span> <span class="token keyword">KEY</span> i1uniq <span class="token punctuation">(</span>c<span class="token punctuation">,</span> d<span class="token punctuation">)</span><span class="token punctuation">,</span>
  <span class="token keyword">KEY</span> i2nonuniq <span class="token punctuation">(</span>e<span class="token punctuation">,</span> f<span class="token punctuation">)</span><span class="token punctuation">,</span>
  <span class="token keyword">KEY</span> i3nonuniq <span class="token punctuation">(</span>g<span class="token punctuation">,</span> h<span class="token punctuation">)</span>
<span class="token punctuation">)</span><span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   For the column and index data required by the algorithm
        described above, query the
   <code class="literal">
    mysql.innodb_index_stats
   </code>
   persistent index
        statistics table for table
   <code class="literal">
    t
   </code>
   . The
   <code class="literal">
    n_diff_pfx%
   </code>
   statistics show the columns that
        are counted for each index. For example, columns
   <code class="literal">
    a
   </code>
   and
   <code class="literal">
    b
   </code>
   are counted for
        the primary key index. For the nonunique indexes, the primary
        key columns (a,b) are counted in addition to the user defined
        columns.
  </p>
  <div class="note" style="margin-left: 0.5in; margin-right: 0.5in;">
   <div class="admon-title">
    Note
   </div>
   <p>
    For additional information about the
    <code class="literal">
     InnoDB
    </code>
    persistent statistics tables, 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>
   </p>
  </div>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa35920832"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token prompt">mysql&gt;</span> <span class="token keyword">SELECT</span> index_name<span class="token punctuation">,</span> stat_name<span class="token punctuation">,</span> stat_description
       <span class="token keyword">FROM</span> mysql<span class="token punctuation">.</span>innodb_index_stats <span class="token keyword">WHERE</span>
       database_name<span class="token operator">=</span><span class="token string">'test'</span> <span class="token operator">AND</span>
       <span class="token keyword">table_name</span><span class="token operator">=</span><span class="token string">'t'</span> <span class="token operator">AND</span>
       stat_name <span class="token operator">like</span> <span class="token string">'n_diff_pfx%'</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>
<span class="token output">  <span class="token punctuation">|</span> index_name <span class="token punctuation">|</span> stat_name    <span class="token punctuation">|</span> stat_description <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>
<span class="token output">  <span class="token punctuation">|</span> PRIMARY    <span class="token punctuation">|</span> n_diff_pfx01 <span class="token punctuation">|</span> a                <span class="token punctuation">|</span></span>
<span class="token output">  <span class="token punctuation">|</span> PRIMARY    <span class="token punctuation">|</span> n_diff_pfx02 <span class="token punctuation">|</span> a,b              <span class="token punctuation">|</span></span>
<span class="token output">  <span class="token punctuation">|</span> i1uniq     <span class="token punctuation">|</span> n_diff_pfx01 <span class="token punctuation">|</span> c                <span class="token punctuation">|</span></span>
<span class="token output">  <span class="token punctuation">|</span> i1uniq     <span class="token punctuation">|</span> n_diff_pfx02 <span class="token punctuation">|</span> c,d              <span class="token punctuation">|</span></span>
<span class="token output">  <span class="token punctuation">|</span> i2nonuniq  <span class="token punctuation">|</span> n_diff_pfx01 <span class="token punctuation">|</span> e                <span class="token punctuation">|</span></span>
<span class="token output">  <span class="token punctuation">|</span> i2nonuniq  <span class="token punctuation">|</span> n_diff_pfx02 <span class="token punctuation">|</span> e,f              <span class="token punctuation">|</span></span>
<span class="token output">  <span class="token punctuation">|</span> i2nonuniq  <span class="token punctuation">|</span> n_diff_pfx03 <span class="token punctuation">|</span> e,f,a            <span class="token punctuation">|</span></span>
<span class="token output">  <span class="token punctuation">|</span> i2nonuniq  <span class="token punctuation">|</span> n_diff_pfx04 <span class="token punctuation">|</span> e,f,a,b          <span class="token punctuation">|</span></span>
<span class="token output">  <span class="token punctuation">|</span> i3nonuniq  <span class="token punctuation">|</span> n_diff_pfx01 <span class="token punctuation">|</span> g                <span class="token punctuation">|</span></span>
<span class="token output">  <span class="token punctuation">|</span> i3nonuniq  <span class="token punctuation">|</span> n_diff_pfx02 <span class="token punctuation">|</span> g,h              <span class="token punctuation">|</span></span>
<span class="token output">  <span class="token punctuation">|</span> i3nonuniq  <span class="token punctuation">|</span> n_diff_pfx03 <span class="token punctuation">|</span> g,h,a            <span class="token punctuation">|</span></span>
<span class="token output">  <span class="token punctuation">|</span> i3nonuniq  <span class="token punctuation">|</span> n_diff_pfx04 <span class="token punctuation">|</span> g,h,a,b          <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></code></pre>
  </div>
  <p>
   Based on the index statistics data shown above and the table
        definition, the following values can be determined:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      <code class="literal">
       n_cols_in_uniq_i
      </code>
      , the total number of all
            columns in all unique indexes not counting the primary key
            columns, is 2 (
      <code class="literal">
       c
      </code>
      and
      <code class="literal">
       d
      </code>
      )
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       n_cols_in_non_uniq_i
      </code>
      , the total number of
            all columns in all nonunique indexes, is 4
            (
      <code class="literal">
       e
      </code>
      ,
      <code class="literal">
       f
      </code>
      ,
      <code class="literal">
       g
      </code>
      and
      <code class="literal">
       h
      </code>
      )
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       n_cols_in_pk
      </code>
      , the number of columns in
            the primary key, is 2 (
      <code class="literal">
       a
      </code>
      and
      <code class="literal">
       b
      </code>
      )
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       n_non_uniq_i
      </code>
      , the number of nonunique
            indexes in the table, is 2 (
      <code class="literal">
       i2nonuniq
      </code>
      and
      <code class="literal">
       i3nonuniq
      </code>
      ))
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       n_part
      </code>
      , the number of partitions, is 1.
     </p>
    </li>
   </ul>
  </div>
  <p>
   You can now calculate
   <code class="literal">
    innodb_stats_persistent_sample_pages
   </code>
   * (2 + 4
        + 2 * (1 + 2)) * 1 to determine the number of leaf pages that
        are scanned. With
   <code class="literal">
    innodb_stats_persistent_sample_pages
   </code>
   set to
        the default value of
   <code class="literal">
    20
   </code>
   , and with a default
        page size of 16
   <code class="literal">
    KiB
   </code>
   (
   <a class="link" href="innodb-parameters.html#sysvar_innodb_page_size">
    <code class="literal">
     innodb_page_size
    </code>
   </a>
   =16384), you
        can then estimate that 20 * 12 * 16384
   <code class="literal">
    bytes
   </code>
   are read for table
   <code class="literal">
    t
   </code>
   , or about 4
   <code class="literal">
    MiB
   </code>
   .
  </p>
  <div class="note" style="margin-left: 0.5in; margin-right: 0.5in;">
   <div class="admon-title">
    Note
   </div>
   <p>
    All 4
    <code class="literal">
     MiB
    </code>
    may not be read from disk, as
          some leaf pages may already be cached in the buffer pool.
   </p>
  </div>
 </div>
 <br/>
</div>
