Understanding MySQL Table and Index Statistics: Persistent vs. Transient

MySQL uses table statistics as the most important reference for its cost‑based optimizer; inaccurate statistics can lead to suboptimal or incorrect execution plans.

Statistics are either non‑persistent (computed in real time) or persistent (saved to disk). Non‑persistent statistics are recomputed on every table access, which can be resource‑intensive for large tables.

Persistent statistics are stored on disk, avoiding repeated computation, surviving server restarts, and can be configured globally or per table.

Key persistent‑related parameters include innodb_stats_persistent (whether persistence is enabled, default on) and innodb_stats_auto_recalc (whether automatic recalculation is enabled, default on).

Update strategy: when a table’s data changes by more than 10 % and the last update was over 10 seconds ago, MySQL recalculates statistics immediately; otherwise the table is placed in an update queue for later processing. Additional parameters such as innodb_stats_include_delete_marked (whether to count rows marked for deletion) and innodb_stats_persistent_sample_pages (default 20 sample pages) affect accuracy and overhead.

Adjust the sample‑page count when query plans are noticeably inaccurate compared to actual distinct counts, or when ANALYZE TABLE runs excessively slowly.

Non‑persistent statistics parameters include innodb_stats_transient_sample_pages (default 8) and innodb_stats_on_metadata (effective only when persistence is disabled).

Accessing metadata such as SHOW TABLE STATUS, SHOW INDEX, or querying information_schema.tables and information_schema.statistics automatically updates statistics, adding extra overhead for large tables.

Table‑level controls: STATS_AUTO_RECALC (default follows server setting, 0 disables auto‑recalc, 1 forces recalculation after 10 % change) and STATS_PERSISTENT (default follows server setting, 0 disables persistence, 1 enables it). STATS_SAMPLE_PAGES defines how many pages are sampled during statistics calculation.

Manual updates are performed with ANALYZE TABLE, preferably during low‑traffic periods.

Persistent table statistics are stored in mysql.innodb_table_stats. Example query shows estimated rows, clustered index pages, and secondary index pages:

mysql> select n_rows,clustered_index_size,sum_of_other_index_sizes from innodb_table_stats where database_name='ytt' and table_name='ytt_sample_persist';

+--------+----------------------+--------------------------+

| n_rows | clustered_index_size | sum_of_other_index_sizes |

+--------+----------------------+--------------------------+

| 356960 | 15162                | 4113                     |

+--------+----------------------+--------------------------+

After running ANALYZE TABLE, the estimates become closer to the actual row count (≈406 644 rows).

Index statistics are stored in mysql.innodb_index_stats. Example output for the PRIMARY index includes distinct value counts, leaf page count, and total size:

mysql> select index_name,stat_name,stat_value,sample_size,stat_description from innodb_index_stats where database_name='ytt' and table_name='ytt_sample_persist' and index_name='PRIMARY';

+------------+--------------+------------+-------------+-----------------------------------+

| index_name | stat_name    | stat_value | sample_size | stat_description                  |

+------------+--------------+------------+-------------+-----------------------------------+

| PRIMARY    | n_diff_pfx01 | 14137      | 20          | i1                                |

| PRIMARY    | n_diff_pfx02 | 75398      | 20          | i1,i2                             |

| PRIMARY    | n_diff_pfx03 | 387202     | 20          | i1,i2,i3                          |

| PRIMARY    | n_leaf_pages | 15708      | NULL        | Number of leaf pages in the index |

| PRIMARY    | size         | 16380      | NULL        | Number of pages in the index      |

+------------+--------------+------------+-------------+-----------------------------------+

Understanding and maintaining accurate statistics enables the optimizer to choose optimal execution plans, especially for complex joins where outdated stats can cause inefficient query ordering.