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

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.

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.