How to Find Empty Tables in MySQL

Over time, as projects extend their functionality, the number of tables in the database grows. Some features may be planned but later removed, or changes may be made as urgent hotfixes that bypass standard procedures. As a result, tables remain for which nobody can explain the purpose, and they lie in databases for years. The problem with them is that MySQL still needs to allocate resources for their existence, including at least file handles and memory. As the number of tables in a typical database reaches hundreds, it becomes an issue to locate and identify these orphaned tables.

The first sign that identifies such tables is that they are empty. They can be found by querying information_schema for all tables that have 0 rows. The database sample will be used for this and the following examples.

SELECT TABLE_NAME 
FROM information_schema.TABLES 
WHERE TABLE_SCHEMA = 'sample' 
    AND TABLE_ROWS = 0;

Alternatively, for tables that use the InnoDB storage engine, this information can be retrieved from the INNODB_TABLESTATS view, which contains low-level status information about InnoDB tables. But unlike the previous example, the NAME column contains the schema and table name in the format sample/empty_table.

SELECT NAME 
FROM information_schema.INNODB_TABLESTATS
WHERE NAME LIKE 'sample%'
    AND NUM_ROWS = 0;

Do not rush to drop them, as they might be used as temporary storage or recreated by batch tasks. The second parameter to check is the table update time. If it is NULL, the table has never been updated.

SELECT TABLE_NAME FROM information_schema.TABLES 
WHERE TABLE_SCHEMA = 'sample' 
    AND TABLE_ROWS = 0
    AND UPDATE_TIME IS NULL;

This option might also be used as a standalone query to find non-empty tables that have never been updated.

SELECT TABLE_NAME FROM information_schema.TABLES 
WHERE TABLE_SCHEMA = 'sample' 
    AND UPDATE_TIME IS NULL;

Be careful when checking the update time, as it may be NULL if the database has been recently upgraded or migrated.

After determining the list of tables that are candidates for removal, verify that there are no references to them in the codebase. Typically, an unused table may be linked to unused code that only retrieves data from it, and removing the tables may cause the system to become non-functional.

As the final step, those tables can be removed to complete the database cleanup.

In this post, several approaches have been reviewed for identifying unused and orphaned tables to perform database cleanup.