content_image/813118.jpg content_image/813119.jpg
Persistent statistics are considered local information, because they relate to the server instance. The innodb_table_stats  and innodb_index_stats  tables are therefore not replicated when automatic statistics recalculation takes place. If you run ANALYZE TABLE  to initiate a synchronous recalculation of statistics, the statement is replicated (unless you suppressed logging for it), and recalculation takes place on replicas. 

## InnoDB Persistent Statistics Tables Example 

The innodb_table_stats  table contains one row for each table. The following example demonstrates the type of data collected. 

Table t1  contains a primary index (columns a , b ) secondary index (columns c , d ), and unique index (columns e ,  f ): 

CREATE TABLE t1 ( a INT, b INT, c INT, d INT, e INT, f INT, PRIMARY KEY (a, b), KEY i1 (c, d), UNIQUE KEY i2uniq (e, f) ) ENGINE=INNODB; 

After inserting five rows of sample data, table t1  appears as follows: 

mysql> SELECT * FROM t1; 

<img src='content_image/813118.jpg'>

To immediately update statistics, run ANALYZE TABLE  (if innodb_stats_auto_recalc  is enabled, statistics are updated automatically within a few seconds assuming that the 10% threshold for changed table rows is reached): 

mysql> ANALYZE TABLE t1; 

<img src='content_image/813119.jpg'>

Table statistics for table t1  show the last time InnoDB  updated the table statistics ( 2014-03-14 14:36:34 ), the number of rows in the table ( 5 ), the clustered index size ( 1  page), and the combined size of the other indexes ( 2  pages). 

mysql> SELECT * FROM mysql.innodb_table_stats WHERE table_name like ’t1’\G *************************** 1. row *************************** database_name: test table_name: t1 last_update: 2014-03-14 14:36:34 n_rows: 5 clustered_index_size: 1 sum_of_other_index_sizes: 2 

The innodb_index_stats  table contains multiple rows for each index. Each row in the innodb_index_stats  table provides data related to a particular index statistic which is named in the stat_name  column and described in the stat_description  column. For example: 

mysql> SELECT index_name, stat_name, stat_value, stat_description 

