The MySQL documentation for innodb_stats_on_metadata says "It can also improve the stability of execution plans for queries that involve InnoDB tables." That is a little vague. What is an example scenario related to this statement?
- Anybody can ask a question
- Anybody can answer
- The best answers are voted up and rise to the top
Here is something you may find surprising
Pick any large InnoDB table. Let's say the table is mydb.mytable.
If you run this query repeatedly, you will notice that the Cardinality column changes. That happens because the InnoDB Storage Engine tries to compute (estimate) the Cardinality based on the BTREE indexes and their leaves rather than brute force counting.
Disabling innodb_stats_on_metadata makes the Cardinality stand still to compute a consistent Explain Plan.
I wrote about this before