When Do InnoDB Table Statistics Update?

InnoDB Table StatisticsInnoDB table statistics are used for JOIN optimizations and helping the MySQL optimizer choose the appropriate index for a query. If a table’s statistics or index cardinality becomes outdated, you might see queries which previously performed well suddenly show up on slow query log until InnoDB again updates the statistics. But when does InnoDB perform the updates aside from the first opening of the table or manually running ANALYZE TABLE on it? The 2 instances below are documented from the MySQL and InnoDB plugin’s manual:

  1. Metadata commands like SHOW INDEX, SHOW TABLE STATUS and SHOW [FULL] TABLES (or their corresponding queries from INFORMATION_SCHEMA.TABLES and INFORMATION_SCHEMA.STATISTICS)
  2. When 1 / 16th of the table or 2Billion rows has been modified, whichever comes first. ./row/row0mysql.c:row_update_statistics_if_needed
If you or an application constantly executes SHOW [FULL] TABLES or SHOW TABLE STATUS on many large tables, this can affect your server especially if the random index dives had to be read from disk. Here is an example of SHOW TABLE STATUS FROM schema taking away a couple of seconds on execution time.

As you can see db1 has about 294 tables and it took the server 12 seconds to update all the tables’ statistics.  Luckily, this can be controlled with Percona Server with the variable innodb_stats_auto_update (from MySQL Community 5.1.17 a similar variable exist called innodb_stats_on_metadata, for Percona Server where both variables exist, both should be 0 if you want to disable the feature). When set to 0, automatic updates to the table statistics (items 1 and 2 above) is disabled unless ANALYZE TABLE is run or during first open of the table.

On my next post, I will discuss the effects of disabling the feature and how you or your application should compensate.

Share this post

Comments (13)

  • Steve Jackson

    I vote for a innodb_stats_on_significant_change variable…


    innodb_stats_on_row_ratio_modified=16 (i.e 1/16th)


    innodb_stats_on_rows_modified = 2000000000


    October 6, 2011 at 10:25 pm
  • Holger Thiel

    Hello Jervin,

    I hope the sentence “When set to 0, …” refers to “innodb_stats_auto_update”. On my MySQL Community servers I always set “innodb_stats_on_metadata = 0” as the information_schema is slow elsewise. And for investigation it is better to know what the old statistics are and then recalculate the new one.


    October 6, 2011 at 10:53 pm
  • Jervin Real


    I meant, on Percona Server where both variables exist – innodb_stats_on_metadata is authoritative, if innodb_stats_auto_update=0, innodb_stats_on_metadata should also be 0 to disable the feature. I agree with you, knowing the previous statistics surely is useful especially when looking for a decent cardinality for your indexes.


    Adding this options may disable the statistics update based on modified rows but not when using the metadata commands.

    October 7, 2011 at 11:15 pm
  • Steve Jackson



    I would like to see stats updates disabled completely and then enabled for particular scenarios with things like innodb_stats_on_row_ratio_modified. I think most dba’s would prefer to be able to control when they update their index stats…

    October 8, 2011 at 11:11 pm
  • Jonathan Valerio

    thanks for sharing. simple yet big help.

    January 17, 2012 at 11:37 pm
  • Aaron Ingram

    You mentioned several metadata commands that will cause table statistics to be updated. Is that the complete list? I’d like to know if “describe” will update table stats.

    June 8, 2012 at 1:45 pm
  • Carsten Dreesbach

    “On my next post, I will discuss the effects of disabling the feature and how you or your application should compensate.”

    Did you get a chance to write that post? I looked and couldn’t find it, would love to hear what you have to say about this.

    February 22, 2013 at 10:46 am
  • Amit Pansare

    Same question as Carsten Dreesbach. Did you get chance to write followup article on this? Really interested to hear your opinions on that.

    February 19, 2014 at 4:17 pm
  • Jervin Real

    Carsten, Amit,

    Thanks for the followup, I have not had the chance to do that yet, but will do really soon 🙂

    February 19, 2014 at 7:10 pm
  • Ben

    Is there a way to precisely update the statistics? I turned on the innodb_stats_auto_update, then execute ‘show table status’ several times. I already knew the correct row count is 34,000,000 for one particular table, but the ‘show table status’ gives me different results each time I ran it, in the same minute, getting 42,000,000, or 31,000,000, or 37,000,000 different answers.

    During this period, the db was idle, there was no any other read/write activity at all.

    April 2, 2014 at 11:33 am
  • Jervin Real


    The InnoDB table statistics are just estimates, as such you cannot really get an accurate row count out of them like MyISAM.

    April 4, 2014 at 1:47 am
  • Carsten Dreesbach

    Hey Jervin, not to be a pest, but I would totally love to read a followup article on this. We’ve recently turned off these two variables and then ran into an issue with queries taking a very long time to execute, which was fixed after running an ANALYZE TABLES. So I’d love to hear other recommendations to keep stats up to date with these settings off in order to avoid running into situations like this.


    June 17, 2014 at 10:14 am
  • Rick James

    5.6.6 (late 2012) changed the game with the introduction of innodb_stats_auto_recalc and Persistent stats (cf ANALYZE). It’s time for a rewrite of this blog?

    August 26, 2015 at 8:56 am

Comments are closed.

Use Percona's Technical Forum to ask any follow-up questions on this blog topic.