<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h3 class="title">
      <a name="innodb-performance-optimizer-statistics">
      </a>
      17.8.10 Configuring Optimizer Statistics for InnoDB
     </h3>
    </div>
   </div>
  </div>
  <div class="toc">
   <dl class="toc">
    <dt>
     <span class="section">
      <a href="innodb-persistent-stats.html">
       17.8.10.1 Configuring Persistent Optimizer Statistics Parameters
      </a>
     </span>
    </dt>
    <dt>
     <span class="section">
      <a href="innodb-statistics-estimation.html">
       17.8.10.2 Configuring Non-Persistent Optimizer Statistics Parameters
      </a>
     </span>
    </dt>
    <dt>
     <span class="section">
      <a href="innodb-analyze-table-complexity.html">
       17.8.10.3 Estimating ANALYZE TABLE Complexity for InnoDB Tables
      </a>
     </span>
    </dt>
   </dl>
  </div>
  <p>
   This section describes how to configure persistent and
      non-persistent optimizer statistics for
   <code class="literal">
    InnoDB
   </code>
   tables.
  </p>
  <p>
   Persistent optimizer statistics are persisted across server
      restarts, allowing for greater
   <a class="link" href="glossary.html#glos_plan_stability" title="plan stability">
    plan stability
   </a>
   and more
      consistent query performance. Persistent optimizer statistics also
      provide control and flexibility with these additional benefits:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      You can use the
      <a class="link" href="innodb-parameters.html#sysvar_innodb_stats_auto_recalc">
       <code class="literal">
        innodb_stats_auto_recalc
       </code>
      </a>
      configuration option to control whether statistics are updated
          automatically after substantial changes to a table.
     </p>
    </li>
    <li class="listitem">
     <p>
      You can use the
      <code class="literal">
       STATS_PERSISTENT
      </code>
      ,
      <code class="literal">
       STATS_AUTO_RECALC
      </code>
      , and
      <code class="literal">
       STATS_SAMPLE_PAGES
      </code>
      clauses with
      <a class="link" href="create-table.html" title="15.1.20 CREATE TABLE Statement">
       <code class="literal">
        CREATE TABLE
       </code>
      </a>
      and
      <a class="link" href="alter-table.html" title="15.1.9 ALTER TABLE Statement">
       <code class="literal">
        ALTER TABLE
       </code>
      </a>
      statements to
          configure optimizer statistics for individual tables.
     </p>
    </li>
    <li class="listitem">
     <p>
      You can query optimizer statistics data in the
      <code class="literal">
       mysql.innodb_table_stats
      </code>
      and
      <code class="literal">
       mysql.innodb_index_stats
      </code>
      tables.
     </p>
    </li>
    <li class="listitem">
     <p>
      You can view the
      <code class="literal">
       last_update
      </code>
      column of the
      <code class="literal">
       mysql.innodb_table_stats
      </code>
      and
      <code class="literal">
       mysql.innodb_index_stats
      </code>
      tables to see when
          statistics were last updated.
     </p>
    </li>
    <li class="listitem">
     <p>
      You can manually modify the
      <code class="literal">
       mysql.innodb_table_stats
      </code>
      and
      <code class="literal">
       mysql.innodb_index_stats
      </code>
      tables to force a
          specific query optimization plan or to test alternative plans
          without modifying the database.
     </p>
    </li>
   </ul>
  </div>
  <p>
   The persistent optimizer statistics feature is enabled by default
      (
   <a class="link" href="innodb-parameters.html#sysvar_innodb_stats_persistent">
    <code class="literal">
     innodb_stats_persistent=ON
    </code>
   </a>
   ).
  </p>
  <p>
   Non-persistent optimizer statistics are cleared on each server
      restart and after some other operations, and recomputed on the
      next table access. As a result, different estimates could be
      produced when recomputing statistics, leading to different choices
      in execution plans and variations in query performance.
  </p>
  <p>
   This section also provides information about estimating
   <a class="link" href="analyze-table.html" title="15.7.3.1 ANALYZE TABLE Statement">
    <code class="literal">
     ANALYZE TABLE
    </code>
   </a>
   complexity, which may
      be useful when attempting to achieve a balance between accurate
      statistics and
   <a class="link" href="analyze-table.html" title="15.7.3.1 ANALYZE TABLE Statement">
    <code class="literal">
     ANALYZE TABLE
    </code>
   </a>
   execution time.
  </p>
 </div>
 <br/>
</div>
