DZone Snippets is a public source code repository. Easily build up your personal collection of code snippets, categorize them with tags / keywords, and share them with the world

Snippets has posted 5883 posts at DZone. View Full User Profile

Find Unused Tables

  • submit to reddit
        // Find the list of all the tables being used from general log and compare them with the table names in Information Schema to know the tables those are not being accessed by any application and hence can be removed.
// Be careful it is not perfect, helps to get an idea about important tables.

unix>> grep "from " /var/log/mysql/mysqld.log | awk -Ffrom '{print $2}' | awk '{print $1}' | sort | uniq | cat > /var/lib/mysql/test/tablelist.txt

mysql> create table test.tableslog (tname varchar(250));
mysql> load data infile 'tablelist.txt' into table tableslog;

mysql> select t1.tname, t2.TABLE_NAME from test.tableslog as t1 LEFT  join information_schema.TABLES as t2 on t1.tname = t2.TABLE_NAME group by t1.tname having t2.TABLE_NAME IS NULL