Since MySQL 5.6 InnoDB has supported persistent index statistics. This means that when you restart MySQL, InnoDB does not have to recalculate the statistics for the tables, rather it can read the statistics from its persistent storage. This has several advantages over the transient statistics, but as it turns out, there is also a catch: MySQL may under some circumstances never get around to update the index statistics. This particularly affects instances that are restarted frequently and tables with a large number of rows.

Information

Persistent statistics are the default, so unless you have explicitly chosen not to use them, then you are.

Background

Before I dive into why there are scenarios where persistent statistics are never updated, it is necessary to recap how persistent statistics work. Every time the statistics are updated, the result is stored in the mysql.innodb_index_stats table with …

The MySQL/MariaDB optimiser likes to know things like the cardinality of an index – that is, the number of distinct values the index holds. For a PRIMARY KEY, which only has unique values, the number is the same as the number of rows.  For an indexed column that is boolean (such as yes/no) the cardinality would be 2.

There’s more to it than that, but the point is that the optimiser needs some statistics from indexes in order to try and make somewhat sane decisions about which index to use for a particular query. The statistics also need to be updated when a significant number of rows have been added, deleted, or modified.

In MyISAM, ANALYZE TABLE does a tablescan where everything is tallied, and the index stats are updated. InnoDB, on the other hand, has always done “index dives”, looking at a small sample and deriving from that. That can be ok as a methodology, but unfortunately the history is awkward. The …