Identifying and Cleaning Unused MySQL Tables Using ProxySQL Stats

When taking over a production database, many tables appear to be temporary, backup, or archived, but their actual usage is unclear, leading to storage waste and additional metadata analysis effort.

The article proposes leveraging ProxySQL's stats_mysql_query_digest table to identify tables that have not been accessed for a long time, since enabling the general log in production is usually avoided due to performance impact.

Implementation steps include:

Exporting all table names from information_schema.TABLES for the target schema.

Iterating over each table name and querying stats_mysql_query_digest for the last seen timestamp, outputting tables with no recent access.

Cleaning the result file to keep only table names.

Manually confirming with project owners whether the identified tables can be removed, then renaming them with a unified suffix (e.g., _unused ) and monitoring for any issues before permanent deletion.

Key command snippets:

mysql -uroot -pxxx -s -e "SELECT TABLE_NAME FROM information_schema.TABLES WHERE TABLE_SCHEMA in ('test');" > table_name.txt

for i in `cat table_name.txt`; do mysql -u admin -p'xxx' -h 127.0.0.1 -P6032 --default-auth=mysql_native_password -s -e "select ifnull((SELECT from_unixtime(last_seen+28800) FROM stats_mysql_query_digest where digest_text like '%${i}%'), '${i}');"; done > ./unused.txt

sed '/:/d' unused.txt   # view without modifying

sed -i '/:/d' unused.txt   # modify file in place

SELECT CONCAT('ALTER TABLE ', TABLE_NAME, ' RENAME ', TABLE_NAME, '_unused;')
FROM INFORMATION_SCHEMA.TABLES
WHERE table_schema IN ('unused') AND TABLE_TYPE='BASE TABLE';

These steps help safely clean up orphaned tables, reclaim storage, and simplify future schema management.