14.3.11 Configuring Optimizer Statistics for InnoDB Configuring Persistent Optimizer Statistics Parameters Configuring Non-Persistent Optimizer Statistics Parameters Estimating ANALYZE TABLE Complexity for InnoDB Tables

This section describes how to configure persistent and non-persistent optimizer statistics for InnoDB tables. Persistent optimizer statistics were introduced in MySQL 5.6.2 and were made the default in MySQL 5.6.6 (innodb_stats_persistent=ON).

Persistent optimizer statistics are persisted across server restarts, allowing for greater plan stability and more consistent query performance. Persistent optimizer statistics also provide control and flexibility with these additional benefits:

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.

This section also provides information about estimating ANALYZE TABLE complexity, which may be useful when attempting to achieve a balance between accurate statistics and ANALYZE TABLE execution time.