MySQL find unused tables

Question

Hi,

I am working with a database which unfortunately has a lot of unused tables and I am trying to clean it up. I'm trying to find a way where I can be 100% confident that a particular table is no longer being used.

After some googling, I still cannot find a good way to do so. I am only able to tell the last writes to a table (INSERT, UPDATE, etc) using:

SHOW TABLE STATUS or running ls -lt on the mysql datadir (can be found by by running SHOW VARIABLES LIKE 'datadir';)

Do you have any other suggestions?

Thanks.

Answer 1

Try that http://forums.mysql.com/read.php?20,298759,299185#msg-299185

Turning on the "general log" and scanning it (programatically!) would tell you who is hitting what tables during the time the log is on. Caution: that file grows fast.

Answer 2

Don't know if it's good : http://forge.mysql.com/tools/tool.php?id=205

Answer 3

Try using the INFORMATION_SCHEMA.TABLES

There is a column called UPDATE_TIME

Check the date in that field

If it is NULL, the table has never been updated since the table's creation

Example : A list of tables not updated in the last 10 days

select table_schema,table_name,create_time,update_time from information_schema.tables where table_schema not in ('information_schema','mysql') and engine is not null and ((update_time < (now() - interval 10 day)) or update_time is null);

Give it a try !!!

www . logicworks . net

http://www.linkedin.com/in/rolandoedwards