MySQL 8.0 Database Administrator — Question 97

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.

Correct answer: A

Explanation

Option A is correct because updating index statistics does indeed require significant I/O resources, making it an expensive operation. Option B is incorrect as index statistics are not solely derived from buffered pages. Option C is not accurate since setting innodb_stats_auto_recalc=ON does not apply to new index creation. Option D is misleading because transient statistics do not inherently improve execution plans based on the sample page increase. Option E is also incorrect because although increasing sample pages may improve scanning speed, it does not directly correlate to increased memory usage.