How to find the Last Time a MySQL Table was Accessed or Updated?

on

When you've inherited a MySQL instance with loads of schemas and tables, many undocumented, and you're wondering "are these tables even used?" knowing how to find the -

  • Last time a MySQL Table was Accessed
  • Last time a MySQL Table was Updated

- is very helpful. And this is very easy to do:

Last time a MySQL Table was Accessed/Updated

SELECT update_time FROM information_schema.tables WHERE table_name = 'tablename'

Note: There are two threads on StackOverflow, 1 for accessed and 1 for updated but they both give the same answer.

UPDATE_TIME is supposed to contain the timestamps of last update (or insert or delete).

The curious of you might do a DESCRIBE information_schema.tables and wonder what CHECK_TIME is. CHECK_TIME is basically the last time 'CHECK TABLE' was run (CHECK TABLE checks a table or tables for errors.)

A query I quite like:

SELECT table_schema,table_name,create_time,update_time,table_comment FROM information_schema.tables WHERE table_schema = 'your_schema' order by 4 DESC


Comments

Post a Comment