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 changed
  • Apr 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 not
  • Jan 16, 2015 : How can I determine which Innodb table is being written?

Tags:

Mysql