updating MySQL statistics
MySQL statistics are used by the optimizer for query plans. Having up to date statistics helps the optimizer “choose” the least expensive way to execute a query.
I know that if I have a query using an index that it should not be and/or performing poorly, I can always run ANALYZE TABLE to update the statistics. But I also wanted to learn when MySQL implicitly updates its internal statistics. So since I can never remember anything – here is a consolidated list taken from a variety of places for me to try and remember things.
- 1. ANALYZE TABLE 
- 2. OPTIMIZE TABLE 
- 3. When a table is opened . (Don’t forget FLUSH TABLES and FLUSH TABLES WITH READ LOCK)
- 4. the mysql client starts if the auto-rehash setting is set on (the default) 
- 5. Metadata commands (SHOW INDEX, SHOW TABLE STATUS and SHOW [FULL] TABLES) or the matching INFORMATION_SCHEMA tables/stats 
- 6. When 1 / 16th of the table or 2Billion rows has been modified, whichever comes first. ./row/row0mysql.c:row_update_statistics_if_needed 
- 7. It is implied that some DDL for a table that causes it to be rebuilt can also trigger an update of statistics. (Ex: ALTER, CREATE INDEX, etc.) 
- 8. Execute myisamchk –stats_method=method_name –analyze 
For more information on how to control the collection of the table statistics see
I am sure I am forgetting other triggers for an update of the table statistics. If anyone knows of any others, please let me know so I can add it to the listing. And if I am incorrect on any of them – be sure to let me know that also.