Find unused MySQL tables

MyISAM

If using MyISAM you can use the following query to list all tables which has not been modified in the last 10 days (or not modified at all):

SELECT table_schema, table_name, create_time, update_time 
FROM information_schema.tables 
WHERE table_schema NOT IN ('information_schema', 'mysql', 'sys', 'performance_schema') 
AND engine IS NOT NULL 
AND ((update_time < (now() - INTERVAL 10 DAY)) OR update_time IS NULL);

InnoDB

This is not supported in InnoDB so we have to check the actual database files on the server when they were last modified. The DB files are located in /var/lib/mysql/<dbname> , but you will have to enter root prompt in order to access this directory.

Run this command to see when the DB tables were last modified:

ls -l --time-style="+%Y-%m-%d" | awk '{print $6,$7}' | grep ibd

Let’s break down the command a bit. We simply use the regular ls to list all files in the directory, then we change the date format to YYYY-MM-DD, then we print only filename+date using awk, then finally we grep (filter) by ibd (because all tables have two files, one .ibd file and one .frm file. – However, only the .ibd file gives the correct modified date).

Example output:

Was this post helpful?

Leave a Reply

Your email address will not be published. Required fields are marked *