MySQL 8.0 Database Administrator — Question 119

Which statement is true about InnoDB persistent index statistics?

Answer options

• A. Updating index statistics is an I/O expensive operation.
• B. Index statistics are calculated from pages buffered in the buffer pool for tables with InnoDB storage engine.
• C. Setting innodb_stats_auto_recalc=ON causes statistics to be updated automatically when a new index is created.
• D. Execution plans based on transient index statistics improve precision when innodb_stats_persistent_sample_pages is increased.
• E. Increasing innodb_stats_persistent_sample_pages determines higher pages scanning speed, at the cost of increased memory usage.
• F. Tables are scanned and index statistics recalculated when an instance is restarted.

Correct answer: A

Explanation

Option A is correct because updating index statistics indeed incurs significant I/O costs. Option B is incorrect as index statistics are not solely based on buffered pages, but rather on the actual data. Options C, D, E, and F contain various inaccuracies regarding the automatic updating of statistics, the impact of sample pages on execution plans, and the recalculation process upon instance restart.