MySQL find unused tables

Question

I am working with a database that, unfortunately, has many unused tables, and I'm trying to clear it. I am trying to find a way that I can be 100% sure that a particular table is no longer in use.

After some searches, I still cannot find a good way to do this. I can only say the last entries in the table (INSERT, UPDATE, etc.) Using:

SHOW TABLE STATE or run ls -ltmysql in datadir (can be found by running SHOW VARIABLES LIKE 'datadir';)

Do you have any other suggestions?

Thank.

Answer 1

Try using INFORMATION_SCHEMA.TABLES

UPDATE_TIME

NULL,

: , 10

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);

!!!

Answer 2

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

" " (!) , , . : .

Answer 3

, , , , , . Mark Leith , - :

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';

MySQL .

Of course, it is required that you enable Performance Schema and that the statistics have not been cleared / truncated for some time.