How to detect inactive tables?
Unfortunately, using the update_time
column from INFORMATION_SCHEMA.TABLES
only works for MyISAM tables. It does not work for InnoDB.
The most effective way to get the last time a table was written is to rely on the OS. You must check the timestamp of the .ibd
or .MYD
files within a database folder.
I wrote earlier posts about how to do this:
Dec 21, 2011
: Fastest way to check if InnoDB table has changedApr 04, 2013
: How to check which tables in DB (MYSQL) updated in last 1 hour / last 1 minute?Jun 03, 2013
: Is there a way to find the least recently used tables in a schema?Sep 25, 2014
: want to find out which databases are used in last 30 days or notJan 16, 2015
: How can I determine which Innodb table is being written?