It’s incredibly useful to know which tables in your MySQL databases are seeing active changes versus those tables that are less frequently updated or entirely static.

To do this, we need to examine DML (Data Manipulation Language) activity—specifically, updates, inserts, and deletes. MySQL offers various mechanisms to gather this information.

This blog will explore two primary options, show you where to find this data, explain how to ensure it’s up-to-date, and offer ideas on its practical applications.

Option 1: mysql.innodb_table_stats

MySQL provides a statistics table called mysql.innodb_table_stats, which includes a last_update column. This column automatically updates when DML operations occur on an InnoDB table.


Let’s look at an example. Suppose we have a table named t1:

mysql> select * from mysql.innodb_table_stats where table_name="t1";
    +---------------+------------+---------------------+--------+----------------------+--------------------------+
    | database_name | table_name | last_update         | n_rows | clustered_index_size | sum_of_other_index_sizes |
    +---------------+------------+---------------------+--------+----------------------+--------------------------+
    | test          | t1         | 2025-06-10 12:21:31 |      1 |                    1 |                        0 |
    +---------------+------------+---------------------+--------+----------------------+--------------------------+

Now, let’s perform some DML operations on t1. After these changes, if we query the table again, we’ll see the last_update timestamp has been updated:

mysql> select * from mysql.innodb_table_stats where table_name="t1";
    +---------------+------------+---------------------+--------+----------------------+--------------------------+
    | database_name | table_name | last_update         | n_rows | clustered_index_size | sum_of_other_index_sizes |
    +---------------+------------+---------------------+--------+----------------------+--------------------------+
    | test          | t1         | 2025-06-10 12:23:24 |      1 |                    1 |                        0 |
    +---------------+------------+---------------------+--------+----------------------+--------------------------+
    1 row in set (0.001 sec)

As you can see, the last_update timestamp accurately reflects the recent DML activity.

It should be clear that you can change this select to look at all tables or all tables in a specific schemas etc. and by changing the where clause and adding some order by statements.  

Option 2: information_schema.tables

Another place you might look for information on table changes is the information_schema.tables view.

This view contains an UPDATE_TIME column. For InnoDB tables that aren’t partitioned, UPDATE_TIME shows a timestamp for the last UPDATE, INSERT, or DELETE operation. With MVCC (Multi-Version Concurrency Control), the timestamp reflects the commit time, which is considered the last update time. However, it’s important to note that these timestamps are not persistent; as they won’t survive a server restart or table eviction from the InnoDB data dictionary cache.


You might find that when you query information_schema.tables, the UPDATE_TIME column is NULL or older than expected, even after performing DMLs. For example:

mysql> select TABLE_NAME, CREATE_TIME, UPDATE_TIME from information_schema.tables where table_name="t1";
    +------------+---------------------+---------------------+
    | TABLE_NAME | CREATE_TIME         | UPDATE_TIME         |
    +------------+---------------------+---------------------+
    | t1         | 2025-06-10 07:17:29 | 2025-06-10 07:17:37 | <<< Never changes
    +------------+---------------------+---------------------+
    1 row in set (0.003 sec)

The reason for this lies in the information_schema_stats_expiry system variable. By default, this variable is set to 86400 seconds (24 hours), meaning the information schema’s cached statistics are only updated once a day. This isn’t very often if you need real-time updates!


To check your current setting:
Show variables like ‘information_schema_stats_expiry’;

+---------------------------------+-------+
    | Variable_name                   | Value |
    +---------------------------------+-------+
    | information_schema_stats_expiry | 86400 |
    +---------------------------------+-------+

While the default is 24 hours, you can change this period from zero to up to a year. If you want to always retrieve the latest statistics directly from the storage engine and bypass cached values, you should set information_schema_stats_expiry to 0.

set persist information_schema_stats_expiry=0;

Note: After changing this variable, you’ll need to disconnect and reconnect your session for the new setting to take effect.

Once you’ve changed the variable and reconnected, the information should be immediately available from your queries.

Let’s see an example with the actor

select table_schema, table_name, engine, create_time, update_time from information_schema.tables where table_name='actor';
    
    +--------------+------------+--------+---------------------+---------------------+
    
    | TABLE_SCHEMA | TABLE_NAME | ENGINE | CREATE_TIME         | UPDATE_TIME         |
    
    +--------------+------------+--------+---------------------+---------------------+
    
    | sakila       | actor      | InnoDB | 2025-06-05 15:22:55 | 2025-06-05 15:37:27 |
    
    +--------------+------------+--------+---------------------+---------------------+
    
    update sakila.actor set first_name='sissi' where first_name='sissy';
    
    Query OK, 1 row affected (0.0060 sec)

Previously, after an update, the UPDATE_TIME might not have changed instantly because the 24-hour expiry hadn’t passed. But with information_schema_stats_expiry set to 0, after reconnecting, you’ll see the immediate update:

See https://dev.mysql.com/doc/refman/8.4/en/server-system-variables.html#sysvar_information_schema_stats_expiry

select table_schema, table_name, engine, create_time, update_time from information_schema.tables where table_name='actor';
    
    +--------------+------------+--------+---------------------+---------------------+
    
    | TABLE_SCHEMA | TABLE_NAME | ENGINE | CREATE_TIME         | UPDATE_TIME         |
    
    +--------------+------------+--------+---------------------+---------------------+
    
    | sakila       | actor      | InnoDB | 2025-06-05 15:22:55 | 2025-06-10 13:52:50 |
    
    +--------------+------------+--------+---------------------+---------------------+

Again – as above – It should be clear that you can change this select to look at all tables or all tables in a specific schemas etc. and by changing the where clause and adding some order by statements.  

Which Option Should You Choose?

Both mysql.innodb_table_stats and information_schema.tables offer ways to monitor DML activity. Let’s weigh their pros and cons.

Advantages of Option 1: mysql.innodb_table_stats

  • Access to up-to-date information: This table generally provides more immediate updates on DML activity.

Disadvantages of Option 1: mysql.innodb_table_stats

  • Requires specific grants to the mysql schema: You’ll need appropriate permissions to query this system table.
  • Potential for internal changes: Table formats within the data dictionary could change in future MySQL versions, and your existing queries might break.
  • Rights to the data dictionary could change: Access privileges might be altered in future releases.

Advantages of Option 2: information_schema.tables

  • Standards-based: It adheres to SQL:2003 standards, making it less prone to breaking changes.
  • Read-only views: The information_schema tables are read-only views, preventing accidental INSERT, UPDATE, or DELETE operations on them.
  • Broad accessibility: For most INFORMATION_SCHEMA tables, each MySQL user has the right to access them, though they can only see rows corresponding to objects for which they have proper access privileges.
  • Single query: You can get the information you need with just one query.

Disadvantages of Option 2: information_schema.tables

  • Requires setting information_schema_stats_expiry: You need to configure this variable appropriately.
  • Potential for DBA intervention: If you don’t have the necessary rights to change information_schema_stats_expiry, you’ll need to request your DBA to modify its value.

Why should you care

Knowing more about the characteristics of changes to the data in your MySQL tables in your database can provide insights such as

  • Performance Optimization:
    • Identify high-change tables and areas of frequent activity to focus optimization efforts.
    • Plan for database growth and capacity based on change patterns.
  •     Data Integrity and Security:
    • Detect unusual or suspicious data modifications.
  •     Backup and Export Strategies:
    • Prioritize frequent exports for frequently changing tables and adjust backup frequency for tables with minimal changes.

Guidance

For general monitoring and where adherence to standards is important, information_schema.tables (Option 2) is often the better choice, provided you configure the information_schema_stats_expiry variable for a time interval that works for your needs.

Setting to zero will give you immediate updates while leveraging a more stable, standards-compliant approach. If you have the necessary privileges and require the absolute quickest updates without needing to adjust system variables, mysql.innodb_table_stats can also be very effective.

As always, thank you for using MySQL!