MySQL: How Query Optimizer Read and Update the Index Statistics

For all the RDBMS, Database Statistics are playing the main role to generate an accurate Query Execution Plan.
A Database Administrator is also very much responsible to make sure that Database Statistics is updated.

MySQL query optimizer is also preparing Query Execution Plan with the help of Statistics Information.
The auto-update Statistics configuration is also very important to keep Statistics updated.

How to enable auto-update Statistics for InnoDB engine?

We can configure innodb_stats_on_metadata variable to enable auto-update Statistics.

When this variable is enabled InnoDB updates statistics when metadata statements such as SHOW TABLE STATUS or SHOW INDEX are run, or when accessing the INFORMATION_SCHEMA.TABLES or INFORMATION_SCHEMA.STATISTICS tables.

Check innodb_stats_on_metadata is ON or OFF:

1	SHOW VARIABLES LIKE 'innodb_stats_on_metadata';

If innodb_stats_on_metadata is OFF, make it ON:

1	SET GLOBAL innodb_stats_on_metadata=ON;

When you perform command like SHOW INDEX, Statisitcs will be update.
Accuracy of statistics is depends on MySQL Server Load.

1	SHOW INDEXES FROM SchemaName.TableName;

You can use below query to check the Index Statistics:

1 2 3 4 5 6 7 8 9 10 11 12

SELECT     Table_Schema     ,Table_Name     ,Non_Unique     ,Index_Schema     ,Seq_In_Index     ,Column_Name     ,Collation     ,Cardinality     ,Index_Type FROM information_schema.statistics WHERE TABLE_SCHEMA='SchemaName';