1

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?

fooledbyprimes
  • 111
  • 4

1 Answers1

2

Here is something you may find surprising

Pick any large InnoDB table. Let's say the table is mydb.mytable.

Run this

SHOW INDEXES FROM 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

Community
  • 1
RolandoMySQLDBA
  • 185,223
  • 33
  • 326
  • 536