<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h4 class="title">
      <a name="innodb-statistics-estimation">
      </a>
      17.8.10.2 Configuring Non-Persistent Optimizer Statistics Parameters
     </h4>
    </div>
   </div>
  </div>
  <a class="indexterm" name="idm46045162087008">
  </a>
  <a class="indexterm" name="idm46045162085936">
  </a>
  <a class="indexterm" name="idm46045162084896">
  </a>
  <p>
   This section describes how to configure non-persistent optimizer
        statistics. Optimizer statistics are not persisted to disk when
   <a class="link" href="innodb-parameters.html#sysvar_innodb_stats_persistent">
    <code class="literal">
     innodb_stats_persistent=OFF
    </code>
   </a>
   or
        when individual tables are created or altered with
   <a class="link" href="create-table.html" title="15.1.20 CREATE TABLE Statement">
    <code class="literal">
     STATS_PERSISTENT=0
    </code>
   </a>
   .
        Instead, statistics are stored in memory, and are lost when the
        server is shut down. Statistics are also updated periodically by
        certain operations and under certain conditions.
  </p>
  <p>
   Optimizer statistics are persisted to disk by default, enabled
        by the
   <a class="link" href="innodb-parameters.html#sysvar_innodb_stats_persistent">
    <code class="literal">
     innodb_stats_persistent
    </code>
   </a>
   configuration option. For information about persistent optimizer
        statistics, 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>
  <h5>
   <a name="innodb-statistics-estimation-update">
   </a>
   Optimizer Statistics Updates
  </h5>
  <p>
   Non-persistent optimizer statistics are updated when:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      Running
      <a class="link" href="analyze-table.html" title="15.7.3.1 ANALYZE TABLE Statement">
       <code class="literal">
        ANALYZE TABLE
       </code>
      </a>
      .
     </p>
    </li>
    <li class="listitem">
     <p>
      Running
      <a class="link" href="show-table-status.html" title="15.7.7.38 SHOW TABLE STATUS Statement">
       <code class="literal">
        SHOW TABLE STATUS
       </code>
      </a>
      ,
      <a class="link" href="show-index.html" title="15.7.7.23 SHOW INDEX Statement">
       <code class="literal">
        SHOW INDEX
       </code>
      </a>
      , or querying the
            Information Schema
      <a class="link" href="information-schema-tables-table.html" title="28.3.38 The INFORMATION_SCHEMA TABLES Table">
       <code class="literal">
        TABLES
       </code>
      </a>
      or
      <a class="link" href="information-schema-statistics-table.html" title="28.3.34 The INFORMATION_SCHEMA STATISTICS Table">
       <code class="literal">
        STATISTICS
       </code>
      </a>
      tables with the
      <a class="link" href="innodb-parameters.html#sysvar_innodb_stats_on_metadata">
       <code class="literal">
        innodb_stats_on_metadata
       </code>
      </a>
      option enabled.
     </p>
     <p>
      The default setting for
      <a class="link" href="innodb-parameters.html#sysvar_innodb_stats_on_metadata">
       <code class="literal">
        innodb_stats_on_metadata
       </code>
      </a>
      is
      <code class="literal">
       OFF
      </code>
      . Enabling
      <a class="link" href="innodb-parameters.html#sysvar_innodb_stats_on_metadata">
       <code class="literal">
        innodb_stats_on_metadata
       </code>
      </a>
      may reduce access speed for schemas that have a large number
            of tables or indexes, and reduce stability of execution
            plans for queries that involve
      <code class="literal">
       InnoDB
      </code>
      tables.
      <a class="link" href="innodb-parameters.html#sysvar_innodb_stats_on_metadata">
       <code class="literal">
        innodb_stats_on_metadata
       </code>
      </a>
      is
            configured globally using a
      <a class="link" href="set.html" title="13.3.6 The SET Type">
       <code class="literal">
        SET
       </code>
      </a>
      statement.
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa98150885"><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">SET</span> <span class="token keyword">GLOBAL</span> innodb_stats_on_metadata<span class="token operator">=</span><span class="token keyword">ON</span></code></pre>
     </div>
     <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_on_metadata">
        <code class="literal">
         innodb_stats_on_metadata
        </code>
       </a>
       only applies when optimizer
       <a class="link" href="glossary.html#glos_statistics" title="statistics">
        statistics
       </a>
       are
              configured to be non-persistent (when
       <a class="link" href="innodb-parameters.html#sysvar_innodb_stats_persistent">
        <code class="literal">
         innodb_stats_persistent
        </code>
       </a>
       is disabled).
      </p>
     </div>
    </li>
    <li class="listitem">
     <p>
      Starting a
      <a class="link" href="mysql.html" title="6.5.1 mysql — The MySQL Command-Line Client">
       <span class="command">
        <strong>
         mysql
        </strong>
       </span>
      </a>
      client with the
      <a class="link" href="mysql-command-options.html#option_mysql_auto-rehash">
       <code class="option">
        --auto-rehash
       </code>
      </a>
      option enabled,
            which is the default. The
      <a class="link" href="mysql-command-options.html#option_mysql_auto-rehash">
       <code class="option">
        auto-rehash
       </code>
      </a>
      option causes all
      <code class="literal">
       InnoDB
      </code>
      tables to be opened, and the open
            table operations cause statistics to be recalculated.
     </p>
     <p>
      To improve the start up time of the
      <a class="link" href="mysql.html" title="6.5.1 mysql — The MySQL Command-Line Client">
       <span class="command">
        <strong>
         mysql
        </strong>
       </span>
      </a>
      client and to updating statistics, you can turn off
      <a class="link" href="mysql-command-options.html#option_mysql_auto-rehash">
       <code class="option">
        auto-rehash
       </code>
      </a>
      using the
      <a class="link" href="mysql-command-options.html#option_mysql_auto-rehash">
       <code class="option">
        --disable-auto-rehash
       </code>
      </a>
      option. The
      <a class="link" href="mysql-command-options.html#option_mysql_auto-rehash">
       <code class="option">
        auto-rehash
       </code>
      </a>
      feature enables automatic name completion of database,
            table, and column names for interactive users.
     </p>
    </li>
    <li class="listitem">
     <p>
      A table is first opened.
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       InnoDB
      </code>
      detects that 1 / 16 of table has
            been modified since the last time statistics were updated.
     </p>
    </li>
   </ul>
  </div>
  <h5>
   <a name="idm46045162042176">
   </a>
   Configuring the Number of Sampled Pages
  </h5>
  <p>
   The MySQL query optimizer uses estimated
   <a class="link" href="glossary.html#glos_statistics" title="statistics">
    statistics
   </a>
   about key
        distributions to choose the indexes for an execution plan, based
        on the relative
   <a class="link" href="glossary.html#glos_selectivity" title="selectivity">
    selectivity
   </a>
   of the
        index. When
   <code class="literal">
    InnoDB
   </code>
   updates optimizer
        statistics, it samples random pages from each index on a table
        to estimate the
   <a class="link" href="glossary.html#glos_cardinality" title="cardinality">
    cardinality
   </a>
   of the
        index. (This technique is known as
   <a class="link" href="glossary.html#glos_random_dive" title="random dive">
    random dives
   </a>
   .)
  </p>
  <p>
   To give you control over the quality of the statistics estimate
        (and thus better information for the query optimizer), you can
        change the number of sampled pages using the parameter
   <a class="link" href="innodb-parameters.html#sysvar_innodb_stats_transient_sample_pages">
    <code class="literal">
     innodb_stats_transient_sample_pages
    </code>
   </a>
   .
        The default number of sampled pages is 8, which could be
        insufficient to produce an accurate estimate, leading to poor
        index choices by the query optimizer. This technique is
        especially important for large tables and tables used in
   <a class="link" href="glossary.html#glos_join" title="join">
    joins
   </a>
   . Unnecessary
   <a class="link" href="glossary.html#glos_full_table_scan" title="full table scan">
    full table scans
   </a>
   for
        such tables can be a substantial performance issue. See
   <a class="xref" href="table-scan-avoidance.html" title="10.2.1.23 Avoiding Full Table Scans">
    Section 10.2.1.23, “Avoiding Full Table Scans”
   </a>
   for tips on tuning such
        queries.
   <a class="link" href="innodb-parameters.html#sysvar_innodb_stats_transient_sample_pages">
    <code class="literal">
     innodb_stats_transient_sample_pages
    </code>
   </a>
   is a global parameter that can be set at runtime.
  </p>
  <p>
   The value of
   <a class="link" href="innodb-parameters.html#sysvar_innodb_stats_transient_sample_pages">
    <code class="literal">
     innodb_stats_transient_sample_pages
    </code>
   </a>
   affects the index sampling for all
   <code class="literal">
    InnoDB
   </code>
   tables and indexes when
   <a class="link" href="innodb-parameters.html#sysvar_innodb_stats_persistent">
    <code class="literal">
     innodb_stats_persistent=0
    </code>
   </a>
   . Be
        aware of the following potentially significant impacts when you
        change the index sample size:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      Small values like 1 or 2 can result in inaccurate
              estimates of cardinality.
     </p>
    </li>
    <li class="listitem">
     <p>
      Increasing the
      <a class="link" href="innodb-parameters.html#sysvar_innodb_stats_transient_sample_pages">
       <code class="literal">
        innodb_stats_transient_sample_pages
       </code>
      </a>
      value might require more disk reads. Values much larger
              than 8 (say, 100), can cause a significant slowdown in the
              time it takes to open a table or execute
      <code class="literal">
       SHOW
              TABLE STATUS
      </code>
      .
     </p>
    </li>
    <li class="listitem">
     <p>
      The optimizer might choose very different query plans
              based on different estimates of index selectivity.
     </p>
    </li>
   </ul>
  </div>
  <p>
  </p>
  <p>
   Whatever value of
   <a class="link" href="innodb-parameters.html#sysvar_innodb_stats_transient_sample_pages">
    <code class="literal">
     innodb_stats_transient_sample_pages
    </code>
   </a>
   works best for a system, set the option and leave it at that
        value. Choose a value that results in reasonably accurate
        estimates for all tables in your database without requiring
        excessive I/O. Because the statistics are automatically
        recalculated at various times other than on execution of
   <a class="link" href="analyze-table.html" title="15.7.3.1 ANALYZE TABLE Statement">
    <code class="literal">
     ANALYZE TABLE
    </code>
   </a>
   , it does not make
        sense to increase the index sample size, run
   <a class="link" href="analyze-table.html" title="15.7.3.1 ANALYZE TABLE Statement">
    <code class="literal">
     ANALYZE TABLE
    </code>
   </a>
   , then decrease
        sample size again.
  </p>
  <p>
   Smaller tables generally require fewer index samples than larger
        tables. If your database has many large tables, consider using a
        higher value for
   <a class="link" href="innodb-parameters.html#sysvar_innodb_stats_transient_sample_pages">
    <code class="literal">
     innodb_stats_transient_sample_pages
    </code>
   </a>
   than if you have mostly smaller tables.
  </p>
 </div>
 <br/>
</div>
