Configure & update the Optimizer Stats gather in MySQL

In MySQL, two type of stats gather you can configure:
1. Persistent : Means after restart the MySQL Server your stats remain persistent for InnonDB Tables offers more plan stability and consistent query performance.
2. Non-persistent : Means after restart the MySQL Server your stats lost and recomputed during table access in memory.

Note: Persistent optimizer statistics were introduced in MySQL 5.6.2 and were made the default in MySQL 5.6.6 (innodb_stats_persistent=ON).

Manually update the statistics in MySQL

ANALYZE TABLE t1;


Enable the Persistent optimizer setting
Configure the following parameter for enable the persistent optimizer setting:

innodb_stats_persistent
-- Enable the Persistent setting: (Default is enabled)
innodb_stats_persistent=ON
-- Disable the Persistent Setting:
innodb_stats_persistent=OFF

innodb_stats_persistent_sample_pages
It is used for number of index pages to sample when estimating cardinality and other statistics for an indexed column.
-- Default value is 20
innodb_stats_persistent_sample_pages=20
Note: Increase the value improves the accuracy of index statistics, which improve the query execution plan, at the cost of increased I/O during the execution.


Enable the Non-Persistent Optimizer Setting
It will stored stats in memory and go away when MySQL is rebooted.

-- Disable the Persistent Setting:
innodb_stats_persistent=OFF

Update stats in non persistent stage:
1. Running ANALYZE TABLE.
2. 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.

Configure Persistent setting at table level
You can define the setting while creating table in Create statement. Following clauses used in syntax: STATS_PERSISTENT, STATS_AUTO_RECALC, STATS_SAMPLE_PAGES.
STATS_PERSISTENT: enable persistent statistics for an InnoDB table. Value 1 means enabled and 0 means disabled. (innodb_stats_persistent parameter for global setting).
STATS_AUTO_RECALC: used for automatically recalculate persistent statistics for an InnoDB table. Value 1 means enabled and 0 means disabled. (innodb_stats_auto_recalc parameter)
STATS_SAMPLE_PAGES: specifies the number of index pages to sample when estimating cardinality and other statistics for an indexed column.

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;


Check the Status of Statistics from Persistent Setting
Check status of Statistics from mysql.innodb_table_stats and mysql.innodb_index_stats

-- Table stats
mysql> select * from mysql.innodb_table_stats;

+---------------+---------------+---------------------+--------+----------------------+--------------------------+
| database_name | table_name    | last_update         | n_rows | clustered_index_size | sum_of_other_index_sizes |
+---------------+---------------+---------------------+--------+----------------------+--------------------------+
| mysql         | component     | 2019-06-18 15:44:51 |      0 |                    1 |                        0 |
| mysql         | gtid_executed | 2019-06-11 16:16:02 |      0 |                    1 |                        0 |
| sys           | sys_config    | 2019-06-11 16:16:10 |      6 |                    1 |                        0 |
| test          | t             | 2019-06-18 15:45:23 |      0 |                    1 |                        0 |
| test5         | t             | 2019-06-19 21:24:54 |     26 |                    1 |                        0 |
| test6         | t             | 2019-06-19 21:20:44 |      5 |                    1 |                        0 |
+---------------+---------------+---------------------+--------+----------------------+--------------------------+

+--------------+------------+------------+-------------------- +--------------+------------+-------------+-----------------------------------+
| database_name| table_name | index_name | last_update         | stat_name    | stat_value | sample_size | stat_description                  |
+--------------+------------+------------+---------------------+--------------+------------+-------------+-----------------------------------+
| test         | test       | test_idx   | 2019-06-27 14:25:38 | n_diff_pfx01 |         10 |           1 | id                                |
| test         | test       | test_idx   | 2019-06-27 14:25:38 | n_diff_pfx02 |         10 |           1 | id,DB_ROW_ID                      |
| test         | test       | test_idx   | 2019-06-27 14:25:38 | n_leaf_pages |          1 |        NULL | Number of leaf pages in the index |
| test         | test       | test_idx   | 2019-06-27 14:25:38 | size         |          1 |        NULL | Number of pages in the index      |
+--------------+------------+------------+---------------------+--------------+------------+-------------+-----------------------------------+