You have set innodb_stats_auto_recalc to OFF. For tables using persistent statistics, what is the outcome of this change?

Question

Options

• AInnoDB no longer automatically updates index statistics after a CREATE TABLE statement.
• BInnoDB no longer automatically updates index statistics after the table structure is altered.
• CInnoDB no longer automatically updates index statistics after 10% of the rows in a table change.
• DInnoDB no longer automatically updates index statistics after an ANALYZE TABLE statement.

• A
  4% (1)
• C
  89% (25)
• D
  7% (2)

Explanation

Setting innodb_stats_auto_recalc=OFF disables the background process that automatically triggers a statistics recalculation when roughly 10% of a table's rows have been inserted, updated, or deleted - that threshold-based trigger is precisely what this variable controls. Options A and B are wrong because CREATE TABLE and ALTER TABLE trigger their own explicit statistics collection as part of DDL execution, which is not governed by innodb_stats_auto_recalc. Option D is wrong because ANALYZE TABLE is a manual command issued by a user or DBA - it always recalculates statistics on demand regardless of any auto-recalc setting.

Memory tip: The word auto in innodb_stats_auto_recalc is the key - it only suppresses the automatic (background, threshold-driven) trigger. Anything explicit - a DDL statement or a manual ANALYZE TABLE - ignores this flag entirely.

No community discussion yet for this question.