Configuring Optimizer Statistics for InnoDB configure automatic statistics recalculation for individual tables by specifying the  STATS_AUTO_RECALC clause when creating or altering a table. Because of the asynchronous nature of automatic statistics recalculation, which occurs in the background, statistics may not be recalculated instantly after running a DML operation that affects more than 10% of a table, even when  innodb_stats_auto_recalc  is enabled. Statistics recalculation can be delayed by few seconds in some cases. If up-to-date statistics are required immediately, run  ANALYZE TABLE  to initiate a synchronous (foreground) recalculation of statistics. If  innodb_stats_auto_recalc  is disabled, you can ensure the accuracy of optimizer statistics by executing the  ANALYZE TABLE  statement after making substantial changes to indexed columns. You might also consider adding  ANALYZE TABLE  to setup scripts that you run after loading data, and running ANALYZE TABLE  on a schedule at times of low activity. When an index is added to an existing table, or when a column is added or dropped, index statistics are calculated and added to the  innodb_index_stats  table regardless of the value of innodb_stats_auto_recalc . innodb_stats_persistent ,  innodb_stats_auto_recalc , and innodb_stats_persistent_sample_pages  are global variables. To override these system- wide settings and configure optimizer statistics parameters for individual tables, you can define STATS_PERSISTENT ,  STATS_AUTO_RECALC , and  STATS_SAMPLE_PAGES  clauses in  CREATE TABLE  or ALTER TABLE  statements. Configuring Optimizer Statistics Parameters for Individual Tables • STATS_PERSISTENT  specifies whether to enable persistent statistics for an  InnoDB  table. The value  DEFAULT  causes the persistent statistics setting for the table to be determined by the innodb_stats_persistent  setting. A value of  1  enables persistent statistics for the table, while a value of  0  disables the feature. After enabling persistent statistics for an individual table, use  ANALYZE TABLE  to calculate statistics after table data is loaded. • STATS_AUTO_RECALC  specifies whether to automatically recalculate persistent statistics. The value  DEFAULT  causes the persistent statistics setting for the table to be determined by the innodb_stats_auto_recalc  setting. A value of  1  causes statistics to be recalculated when 10% of table data has changed. A value  0  prevents automatic recalculation for the table. When using a value of 0, use  ANALYZE TABLE  to recalculate statistics after making substantial changes to the table. • STATS_SAMPLE_PAGES  specifies the number of index pages to sample when cardinality and other statistics are calculated for an indexed column, by an  ANALYZE TABLE  operation, for example. All three clauses are specified in the following  CREATE TABLE  example: The optimizer uses estimated statistics about key distributions to choose the indexes for an execution plan, based on the relative selectivity of the index. Operations such as  ANALYZE TABLE  cause  InnoDB Configuring the Number of Sampled Pages for InnoDB Optimizer Statistics CREATE TABLE ‘t1‘ ( ‘id‘ int(8) NOT NULL auto_increment, ‘data‘ varchar(255), ‘date‘ datetime, PRIMARY KEY (‘id‘), INDEX ‘DATE_IX‘ (‘date‘) ) ENGINE=InnoDB, STATS_PERSISTENT=1, STATS_AUTO_RECALC=1, STATS_SAMPLE_PAGES=25; 3132