How can I tell when a MySQL table was last updated?

In the footer of my page, I would like to add something like "last updated the xx/xx/200x" with this date being the last time a certain mySQL table has been updated.

What is the best way to do that? Is there a function to retrieve the last updated date? Should I access to the database every time I need this value?

Solution 1:

In later versions of MySQL you can use the information_schema database to tell you when another table was updated:

SELECT UPDATE_TIME
FROM   information_schema.tables
WHERE  TABLE_SCHEMA = 'dbname'
   AND TABLE_NAME = 'tabname'

This does of course mean opening a connection to the database.

An alternative option would be to "touch" a particular file whenever the MySQL table is updated:

On database updates:

• Open your timestamp file in O_RDRW mode
• close it again

or alternatively

• use touch(), the PHP equivalent of the utimes() function, to change the file timestamp.

On page display:

• use stat() to read back the file modification time.

Solution 2:

I'm surprised no one has suggested tracking last update time per row:

mysql> CREATE TABLE foo (
  id INT PRIMARY KEY
  x INT,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP 
                     ON UPDATE CURRENT_TIMESTAMP,
  KEY (updated_at)
);

mysql> INSERT INTO foo VALUES (1, NOW() - INTERVAL 3 DAY), (2, NOW());

mysql> SELECT * FROM foo;
+----+------+---------------------+
| id | x    | updated_at          |
+----+------+---------------------+
|  1 | NULL | 2013-08-18 03:26:28 |
|  2 | NULL | 2013-08-21 03:26:28 |
+----+------+---------------------+

mysql> UPDATE foo SET x = 1234 WHERE id = 1;

This updates the timestamp even though we didn't mention it in the UPDATE.

mysql> SELECT * FROM foo;
+----+------+---------------------+
| id | x    | updated_at          |
+----+------+---------------------+
|  1 | 1235 | 2013-08-21 03:30:20 | <-- this row has been updated
|  2 | NULL | 2013-08-21 03:26:28 |
+----+------+---------------------+

Now you can query for the MAX():

mysql> SELECT MAX(updated_at) FROM foo;
+---------------------+
| MAX(updated_at)     |
+---------------------+
| 2013-08-21 03:30:20 |
+---------------------+

Admittedly, this requires more storage (4 bytes per row for TIMESTAMP).
But this works for InnoDB tables before 5.7.15 version of MySQL, which INFORMATION_SCHEMA.TABLES.UPDATE_TIME doesn't.

Solution 3:

I don't have information_schema database, using mysql version 4.1.16, so in this case you can query this:

SHOW TABLE STATUS FROM your_database LIKE 'your_table';

It will return these columns:

| Name      | Engine | Version | Row_format | Rows | Avg_row_length 
| Data_length | Max_data_length | Index_length | Data_free | Auto_increment 
| Create_time         | Update_time         | Check_time | Collation         
| Checksum | Create_options | Comment |

As you can see there is a column called: "Update_time" that shows you the last update time for your_table.