PROLOGUE

As I mentioned earlier in Adding new tables -- memory usage increases, adding new tables increases memory usage. That being the case, tables that are not in active use would still consume memory in the INFORMATION_SCHEMA.

Determining what is active is an arbitrary process. If you need to see when was the last time a table had written changes, you have to find out based on the Storage Engine of each table.

MyISAM

Learning the last time a MyISAM table had written changes is straightforward.

Suppose you need to see all MyISAM tables that have not been written to in over a month. Simply run this query again INFORMATION_SCHEMA.tables:

SELECT table_schema,table_name,update_time FROM information_schema.tables
WHERE table_schema NOT IN ('information_schema','mysql','performance_schema')
AND engine='MyISAM' and update_time > (NOW() - INTERVAL 1 MONTH);

Note: update_time for a MyISAM table is the timestamp of the .MYD file. If you run ANALYZE TABLE mydb.mytable;, the index statistics are written to the .MYI file. The .MYI's file timestamp is not reflected in the INFORMATION_SCHEMA.

InnoDB

Getting InnoDB timestamps is quite a challenge because InnoDB does not store update_time in the INFORMATION_SCHEMA. If innodb_file_per_table is disabled, everything InnoDB and its grandmother is encased in the system tablespace file ibdata1. Gettting the timestamp of ibdata1 gives you the last time any write was received. That could be anything

• INSERT, UPDATE, DELETE (altering data and index pages)
• ANALYZE TABLE (altering index pages)
• SELECT (data snapshots via MVCC)

Therefore, with innodb_file_per_table disabled, there is not way to know.

On the other hand, if innodb_file_per_table is enabled, the question remains: HOW DO YOU FIND OUT THE LAST TIME AN INNODB TABLE WAS WRITTEN ?

You must check the file timestamp of the .ibd.

Rather than reinvent the wheel, please read my posts on how to get that info

• Dec 21, 2011 : Fastest way to check if InnoDB table has changed
• Jun 03, 2013 : Is there a way to find the least recently used tables in a schema?

EPILOGUE

The main point to keep in mind is to find out timestamp of the table and subtract it from NOW() (or UNIX_TIMESTAMP(NOW()) from the OS point-of-view). You have to arbitrarily determine how old is too old for an inactive table.