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.
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
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
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
Rather than reinvent the wheel, please read my posts on how to get that info
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.