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:
SHOW VARIABLES LIKE 'innodb_stats_on_metadata';
If innodb_stats_on_metadata is OFF, make it ON:
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.
SHOW INDEXES FROM SchemaName.TableName;
You can use below query to check the Index Statistics:
Please share your ideas and opinions about this topic with me, your contribution will add true value to this topic.
If anyone has doubts on this topic then please do let me know by leaving comments or send me an email.
If you like this post, then please share it with others.
Please follow dbrnd.com, I will share my experience towards the success of Database Research and Development Activity.
I put up a post every day, please keep reading and learning.
Discover Yourself, Happy Blogging !
Anvesh M. Patel.