MySQL find unused tables

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.

I know this is an old question, but there doesn't seem to be a proper answer and I was directed here by my own search for an answer. As per Mark Leith's blog post about unused tables and indexes one should be able to do something like this:

SELECT
    t.*
FROM performance_schema.table_io_waits_summary_by_table t
WHERE
    t.COUNT_STAR = 0
    AND t.OBJECT_SCHEMA = '<your-schema-name-goes-here>'
    AND t.OBJECT_TYPE = 'TABLE';

Official documentation about the topic from MySQL gives more details.

It does of course require that you've enabled Performance Schema and that the statistics haven't been cleared/truncated for some time.

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 !!!

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.

I am surprised at how little support MySQL has for identifying unused tables. As noted in a comment to this question, at least one promising method turns out to work with MyISAM but not InnoDB tables.

However, I may have found a reliable means of identifying unused InnoDB tables, at least as of MySQL 5.7.26. This method takes advantage of MySQL's file_summary_by_instance table, which aggregates information about I/O operations by filename.

Log in to mysql and:

mysql> SELECT * FROM performance_schema.file_summary_by_instance\G

This prints values such as COUNT_READ for all files in all databases. You can narrow the search in various ways. For example, to show all values for database file /var/lib/mysql/production/user.ibd,

mysql> SELECT * FROM performance_schema.file_summary_by_instance where FILE_NAME = '/var/lib/mysql/production/user.ibd'\G

You can also select only the values you are interested in, e.g.,

mysql> SELECT FILE_NAME,COUNT_STAR,COUNT_READ,COUNT_WRITE,COUNT_MISC FROM performance_schema.file_summary_by_instance where FILE_NAME = '/var/lib/mysql/production/user.ibd'\G

The above queries allow you to monitor usage counts of various types on all files used by your instance of MySQL. However, to tell whether a file is being used at all you probably want to reset these counts. Fortunately,

TRUNCATE TABLE is permitted for file I/O summary tables. It resets the summary columns to zero rather than removing rows.

So you can,

mysql> truncate table file_summary_by_instance;

and then watch which files' counts get incremented.

One caution: because some tables may only be read at application startup, you may want to restart the application (or even the server) and see whether this increases the access counts.

For more details on the file_summary_by_instance table, see https://dev.mysql.com/doc/refman/5.7/en/file-summary-tables.html

Well, this questions has been asked since way back, but I hope this answer will help someone in the future.

SHOW TABLE STATUS where `Rows` = 0;