#Mysql optimizer update

When does InnoDB's recalculation of statistics become visible? If you are not interested in my proof for this, you may skip to the end of the blog where I will provide some recommendations for running ANALYZE TABLE. The conclusion is that there are really no guarantees as to when the query optimizer will see statistics refreshed by InnoDB, and that you need to regularly run ANALYZE TABLE to guarantee this.

When a new connection accesses the table, the updated statistics will be visible to all connections.Īs you already may have guessed, none of these statements are actually true, and below I will present a small experiment that shows this.

The updated statistics will only be visible to new connections (i.e., sessions that are started after the statistics was updated).

I must admit that I, for a long time, believed in the myths I was told about when updates to InnoDB's index statistics become visible to the query optimizer.

However, the matter is even worse! In this blog post I will show that even when the automatic recalculation is performed, there are no guarantees as to when the server will see the changes.

#Mysql optimizer update

This blog post is inspired by Jesper's recent blog post on how the automatic update of InnoDB persistent statistics may never trigger if servers are restarted frequently.