Real‑time Update of AUTO_INCREMENT in INFORMATION_SCHEMA.TABLES on MySQL 8.0

When a colleague asked whether the INFORMATION_SCHEMA.TABLES.AUTO_INCREMENT value is updated promptly after inserting rows, the answer is yes. The update depends on MySQL’s statistics caching mechanism, which is governed by the information_schema_stats_expiry system variable.

Basic Logic of Table Information Update

By default MySQL retrieves cached statistics from the internal tables mysql.index_stats and mysql.table_stats instead of reading directly from the storage engine. If the cached data is missing or expired, MySQL fetches fresh statistics from the engine and stores them back into those internal tables. Subsequent queries use the cached values until they expire.

Note that after a MySQL restart or the first opening of the internal stats tables, the caches are not refreshed automatically.

Core Parameter

The key variable is information_schema_stats_expiry , which defaults to 86400 seconds (24 hours). This setting determines how often MySQL re‑collects statistics for tables and stores them in the INFORMATION_SCHEMA tables such as TABLES.AUTO_INCREMENT , TABLES.TABLE_ROWS , etc.

STATISTICS.CARDINALITY
TABLES.AUTO_INCREMENT
TABLES.AVG_ROW_LENGTH
TABLES.CHECKSUM
TABLES.CHECK_TIME
TABLES.CREATE_TIME
TABLES.DATA_FREE
TABLES.DATA_LENGTH
TABLES.INDEX_LENGTH
TABLES.MAX_DATA_LENGTH
TABLES.TABLE_ROWS
TABLES.UPDATE_TIME

If the variable is set to 0 , MySQL reads statistics directly from the storage engine on every request, which can impact performance.

Statistics are not refreshed in the following situations:

When the cached statistics have not yet expired.

When information_schema_stats_expiry is set to 0 .

When the server runs in read‑only, super‑read‑only, or transaction‑read‑only mode, or when innodb_read_only is enabled.

When querying data from the Performance Schema.

Test Procedure (MySQL 8.0.30)

First, a test table sbtest1 is created:

CREATE TABLE `sbtest1` (
  `id` int NOT NULL AUTO_INCREMENT,
  `k` int NOT NULL DEFAULT '0',
  `c` char(120) NOT NULL DEFAULT '',
  `pad` char(60) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

Initial checks show that information_schema.tables reports the same AUTO_INCREMENT value as the table definition (e.g., 1200006).

SELECT table_name, AUTO_INCREMENT FROM information_schema.tables WHERE table_name='sbtest1';
+------------+----------------+
| TABLE_NAME | AUTO_INCREMENT |
+------------+----------------+
| sbtest1    |        1200006 |
+------------+----------------+

After inserting a row, the table’s AUTO_INCREMENT increments to 1200007, but the value in information_schema.tables remains unchanged because the statistics cache has not expired.

Setting information_schema_stats_expiry=0 forces immediate refresh:

SET information_schema_stats_expiry=0;
SHOW VARIABLES LIKE 'information_schema_stats_expiry';
+-------------------------------+-------+
| Variable_name                 | Value |
+-------------------------------+-------+
| information_schema_stats_expiry| 0    |
+-------------------------------+-------+

After the change, a new query to information_schema.tables returns the updated AUTO_INCREMENT value (1200007). Subsequent inserts continue to be reflected instantly, and even manual ALTER TABLE to set a specific AUTO_INCREMENT value is immediately visible.

Conclusion

MySQL 8.0’s statistics infrastructure allows the AUTO_INCREMENT column in information_schema.tables to be refreshed in real time when the information_schema_stats_expiry parameter is set to 0 . For production monitoring, directly querying the table’s primary‑key column may be more practical, but the INFORMATION_SCHEMA mechanism provides a powerful, configurable way to obtain up‑to‑date metadata.

References

[1] Information Statistics Mechanism: https://dev.mysql.com/doc/refman/8.0/en/information-schema-optimization.html