How to Find When Table Was Last Updated in MySQL

MySQL is a powerful database system used by millions of organizations and websites all over the world. They are often used to implement high traffic transactional database schemas. While using MySQL, database administrators may often need to find when table was last updated in MySQL. This is mainly required to debugging purposes. In this article, we will learn how to get last modified date of table in MySQL.

How to Find When Table Was Last Updated in MySQL

In MySQL, every table’s last modified datetime information is stored in information_schema.tables table. Information_schema is a system-generated database created at the time of MySQL installation. It is automatically updated by MySQL as and when different kinds of operations are performed in any of the MySQL databases created on the server. It contains a table named tables that contains many useful pieces of information. You can easily get the last modified time of a table using the following SQL query. Replace database_name and table_name below with the names of your database and table respectively.

SELECT UPDATE_TIME
FROM   information_schema.tables
WHERE  TABLE_SCHEMA = 'database_name'
   AND TABLE_NAME = 'table_name'

Please note, the above query is available for MyISAM database of almost all versions but it is supported for InnoDB database only starting MySQL 5.7.2+.

Alternatively, you can also use the following query, which uses the same UPDATE_TIME column, under the hood.

SHOW TABLE STATUS
   [{FROM | IN} db_name]
   [LIKE 'pattern' | WHERE expr]

So you can use this to get modified datetime for your table. But this shows information for all tables in a given database.

show table status from database_name

Of course there are several other non-database ways to do the same thing, but none so easy. For example, you can create an empty file on your system and run the touch command on this file, every time you modify your table. You can also use other system commands to track changes in this file. But it is easier to get this information via SQL queries.

In this article, we have learnt how to find when table was last updated in MySQL.

Also read:

How to Truncate All Tables of Database in MySQL
How to Get Primary Key of Newly Inserted Row in PostgreSQL
How to Get Primary Key of Newly Inserted Row in MySQL
How to Find Tables With Column Name in MySQL
How to Find my.cnf File Location in MySQL