Configuring Optimizer Statistics for InnoDB •Running  ANALYZE TABLE . •Running  SHOW TABLE STATUS ,  SHOW INDEX , or querying the  INFORMATION_SCHEMA.TABLES  or INFORMATION_SCHEMA.STATISTICS  tables with the  innodb_stats_on_metadata  option enabled. •Starting a  mysql  client with the  --auto-rehash  option enabled, which is the default. The  auto- rehash  option causes all  InnoDB  tables to be opened, and the open table operations cause statistics to be recalculated. •A table is first opened. • InnoDB  detects that 1 / 16 of table has been modified since the last time statistics were updated. •Small values like 1 or 2 can result in inaccurate estimates of cardinality. •Increasing the  innodb_stats_transient_sample_pages  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  SHOW TABLE STATUS . •The optimizer might choose very different query plans based on different estimates of index selectivity. 3139 The value of  innodb_stats_transient_sample_pages  affects the index sampling for all  InnoDB tables and indexes when  innodb_stats_persistent=0 . Be aware of the following potentially significant impacts when you change the index sample size: 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 innodb_stats_transient_sample_pages . 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 joins. Unnecessary full table scans for such tables can be a substantial performance issue. See Section 8.2.1.23, “Avoiding Full Table Scans”  for tips on tuning such queries.  innodb_stats_transient_sample_pages  is a global parameter that can be set at runtime. The MySQL query optimizer uses estimated statistics about key distributions to choose the indexes for an execution plan, based on the relative selectivity of the index. When  InnoDB  updates optimizer statistics, it samples random pages from each index on a table to estimate the cardinality of the index. (This technique is known as random dives.) To improve the start up time of the  mysql  client and to updating statistics, you can turn off  auto- rehash  using the  --disable-auto-rehash  option. The  auto-rehash  feature enables automatic name completion of database, table, and column names for interactive users. innodb_stats_on_metadata  only applies when optimizer statistics are configured to be non-persistent (when  innodb_stats_persistent  is disabled). The default setting for  innodb_stats_on_metadata  is  OFF . Enabling innodb_stats_on_metadata  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  InnoDB  tables. innodb_stats_on_metadata  is configured globally using a  SET  statement. SET GLOBAL innodb_stats_on_metadata=ON Note Configuring the Number of Sampled Pages