Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question

1 Answer 1

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

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.