Cleaning up unnecessary tables in MySQL

Clean unnecessary tables in MySQL

Why clean unnecessary tables?

Over time, databases can accumulate tables that are no longer used: test tables, old data, temporary tables, or obsolete structures. Cleaning these tables helps to:

• Reduce database size
• Improve query performance
• Facilitate maintenance
• Reduce backup time
• Free up disk space

Identify unnecessary tables

1. List all tables in a database

mysql -u root -p
USE database_name;
SHOW TABLES;

2. See the size of each table

SELECT 
    table_name AS 'Table',
    ROUND(((data_length + index_length) / 1024 / 1024), 2) AS 'Size (MB)'
FROM information_schema.TABLES
WHERE table_schema = 'database_name'
ORDER BY (data_length + index_length) DESC;

3. See empty tables

SELECT 
    table_name AS 'Table',
    table_rows AS 'Rows'
FROM information_schema.TABLES
WHERE table_schema = 'database_name'
AND table_rows = 0;

Common tables that can be deleted

• Test tables: test_*, tmp_*, temp_*
• Uninstalled plugin tables: In WordPress, Joomla, etc.
• Old log tables: Logs that are no longer needed
• Obsolete cache tables: Cache that regenerates automatically
• Old backup tables: Backups that are already elsewhere

Safe process to delete tables

1. Make backup before deleting

Before deleting any table, make a full backup:

mysqldump -u root -p database_name > backup_before_cleanup.sql

2. Verify table is not in use

Check your application, source code, or documentation to confirm the table is not being used.

3. Check dependencies

Some tables may have relationships with others. Check foreign keys:

SELECT 
    TABLE_NAME,
    COLUMN_NAME,
    CONSTRAINT_NAME,
    REFERENCED_TABLE_NAME,
    REFERENCED_COLUMN_NAME
FROM information_schema.KEY_COLUMN_USAGE
WHERE TABLE_SCHEMA = 'database_name'
AND REFERENCED_TABLE_NAME = 'table_name';

4. Delete the table

DROP TABLE table_name;

To delete multiple tables:

DROP TABLE table1, table2, table3;

Clean WordPress tables (example)

WordPress sometimes leaves tables from uninstalled plugins. To identify them:

-- See all WordPress tables
SHOW TABLES LIKE 'wp_%';

-- See tables that are not WordPress standard
-- Standard tables are: wp_posts, wp_users, wp_options, wp_comments, etc.
-- Plugin tables usually have names like wp_plugin_name_*

Before deleting, check in the plugin code or documentation.

Clean temporary tables

Temporary tables usually have names like tmp_*, temp_* or test_*:

-- List temporary tables
SELECT table_name 
FROM information_schema.TABLES 
WHERE table_schema = 'database_name' 
AND (table_name LIKE 'tmp_%' 
     OR table_name LIKE 'temp_%' 
     OR table_name LIKE 'test_%');

If you're sure they're not needed, delete them:

DROP TABLE tmp_table1, tmp_table2;

Clean old data without deleting the table

If the table is needed but you want to delete old data:

-- Delete old records (example: more than 1 year old)
DELETE FROM table_name 
WHERE date < DATE_SUB(NOW(), INTERVAL 1 YEAR);

After deleting data, optimize the table:

OPTIMIZE TABLE table_name;

Script to identify large and empty tables

You can use this script to identify tables that take up a lot of space but are empty or have few data:

SELECT 
    table_name AS 'Table',
    table_rows AS 'Rows',
    ROUND(((data_length + index_length) / 1024 / 1024), 2) AS 'Size (MB)',
    ROUND((data_length / 1024 / 1024), 2) AS 'Data (MB)',
    ROUND((index_length / 1024 / 1024), 2) AS 'Indexes (MB)'
FROM information_schema.TABLES
WHERE table_schema = 'database_name'
AND table_rows < 100
AND (data_length + index_length) > 10485760  -- More than 10MB
ORDER BY (data_length + index_length) DESC;

Important precautions

• Never delete tables without backup: Always make a full backup first.
• Check dependencies: Some tables may be needed even if not directly used.
• Test in development first: If possible, test deletion in a development environment.
• Document what you delete: Keep a record of deleted tables and when.
• Don't delete system tables: Never delete tables from mysql, information_schema or performance_schema.

Recover space after deleting tables

After deleting tables, optimize the database to recover space:

-- Optimize all remaining tables
mysqlcheck -u root -p --optimize database_name

Or from MySQL:

USE database_name;
OPTIMIZE TABLE table1, table2, table3;

Verify freed space

To see the total size of the database before and after:

SELECT 
    table_schema AS 'Database',
    ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS 'Total Size (MB)'
FROM information_schema.TABLES
WHERE table_schema = 'database_name'
GROUP BY table_schema;

Need help?

If you have questions about which tables to delete or need assistance cleaning your database, open a ticket from the billing.baires.host panel or contact us for support.

You can also reach us through our social media:

• Instagram: @baires_host
• Discord (active support): https://discord.gg/dzjauatAFN
• Linktree: https://linktr.ee/baires.host