MySQL find unused tables

Question

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

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.