CODEWITHSUNDEEP
mysqldatabase
Calvin
Calvin

Reputation: 710

Cleaning a MySQL 5.0 database of unused tables

At my summer internship this year, I've been given the project of trying to clean up our database. I've found a solution on here that tells me to use the Information_Schema.tables UPDATE_TIME column, HOWEVER, 95% of the UPDATE_TIME fields are NULL, even on tables I know our programs hook into and update on a daily basis. So I was wondering if there was another simple way to try to find what tables haven't been used in a while.

Thanks!

Upvotes: 2

Views: 520

Answers (1)

Michael Mior
Michael Mior

Reputation: 28753

Assuming none of the tables are read-only, you could set up triggers so that when values are deleted or updated, a row is updated in a logging table. Then you can leave it running for an appropriate amount of time to see which tables haven't been updated.

I don't believe there's a way to directly check which tables have been used recently. If the tables or MyISAM, or the server is using innodb_file_per_table, you may be able to look at the last modified times of the tables in the data directory. I would suspect this wouldn't produce any false positives with respect to infrequently accessed tables, but you may well get some false negatives.

Upvotes: 2

Related Questions