Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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:

  • table is opened for first time
  • query is run against table
  • ANALYZE TABLE
  • size of table changes by some threshold
share|improve this question
add comment

1 Answer

up vote 2 down vote accepted

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.

share|improve this answer
1  
Thanks. If It turn it off, when will index statistics be updated? –  Eric Rath Mar 26 '12 at 18:52
    
You have to do ANALYZE TABLE then –  RolandoMySQLDBA Mar 26 '12 at 18:56
    
I found a Percona reference --mysqlperformanceblog.com/2011/10/06/… -- that suggests the statistics are updated when the table size changes by 1/16, but I can't find any corroboration for that statement. Do you know if that's true or not? –  Eric Rath Mar 26 '12 at 19:04
    
From that link, it says : "2.When 1 / 16th of the table or 2Billion rows has been modified, whichever comes first. ./row/row0mysql.c:row_update_statistics_if_needed". You may have to download the source code and go find the file row0mysql.c and look around. That is the deepest corrobrative evidence there is. –  RolandoMySQLDBA Mar 26 '12 at 19:29
1  
@All: Also see changes in 5.6.2 blogs.innodb.com/wp/2011/04/… –  gbn Mar 27 '12 at 7:23
show 2 more comments

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.