In a vanilla build of MySQL 5.5.20, when are InnoDB table index statistics updated? What events trigger such updates? I've seen comments suggesting that the following might trigger it:
You should look for this variable
mysql> show variables like '%metadata%';
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| innodb_stats_on_metadata | ON |
+--------------------------+-------+
1 row in set (0.00 sec)
mysql>
According to the MySQL Docs, when innodb_stats_on_metadata is set (by default), InnoDB updates statistics during metadata statements such as SHOW TABLE STATUS or SHOW INDEX, or when accessing the INFORMATION_SCHEMA tables TABLES or STATISTICS. (These updates are similar to what happens for ANALYZE TABLE.) When disabled, InnoDB does not update statistics during these operations. Disabling this variable can improve access speed for schemas that have a large number of tables or indexes. It can also improve the stability of execution plans for queries that involve InnoDB tables.
Once disabled, you would have to run ANALYZE TABLE on the InnoDB tables of your choice. Make sure you have SELECT and INSERT privileges.
row_update_statistics_if_needed that behaves as described: it calls another method dict_update_statistics if the table-update counter is over 2 billion, or if the table-update counter is greater than 1/16 of the number of rows in the table. This row_update_statistics_if_needed method is called on insert, update, and cascade-update.
Commented
Mar 26, 2012 at 19:53