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. 1. ANALYZE TABLE [1]
  2. 2. OPTIMIZE TABLE [2]
  3. 3. When a table is opened [3]. (Don’t forget FLUSH TABLES and FLUSH TABLES WITH READ LOCK)
  4. 4. the mysql client starts if the auto-rehash setting is set on (the default) [3]
  5. 5. Metadata commands (SHOW INDEX, SHOW TABLE STATUS and SHOW [FULL] TABLES) or the matching INFORMATION_SCHEMA tables/stats [4]
  6. 6. When 1 / 16th of the table or 2Billion rows has been modified, whichever comes first. ./row/row0mysql.c:row_update_statistics_if_needed [4]
  7. 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.) [1][2][6]
  8. 8. Execute myisamchk –stats_method=method_name –analyze [5]

For more information on how to control the collection of the table statistics see
– http://dev.mysql.com/doc/refman/5.5/en/myisam-index-statistics.html
– http://dev.mysql.com/doc/innodb/1.1/en/innodb-other-changes-statistics-estimation.html

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.

References:
[1] http://dev.mysql.com/doc/refman/5.5/en/analyze-table.html
[2] http://dev.mysql.com/doc/refman/5.5/en/optimize-table.html
[3] http://dev.mysql.com/doc/innodb/1.1/en/innodb-other-changes-statistics-estimation.html
[4] http://www.mysqlperformanceblog.com/2011/10/06/when-does-innodb-update-table-statistics-and-when-it-can-bite/
[5] http://dev.mysql.com/doc/refman/5.5/en/myisam-index-statistics.html
[6] http://dev.mysql.com/doc/refman/5.6/en/innodb-performance.html#innodb-persistent-stats