Before You Begin: Backup Everything
WordPress databases are fragile. A single erased table or incorrect WHERE clause can destroy your site. Before running any cleanup, export a full database backup using phpMyAdmin or your hosting control panel. If anything goes wrong, you can restore it in minutes. Do not skip this step. Many WordPress hosting providers offer one-click backups. Use that feature before proceeding. If your host does not offer backups, manually export via phpMyAdmin and download the SQL file to your computer.
What Causes Database Bloat
Your WordPress database grows larger over time due to:
- Post revisions: Every draft save keeps a full copy of your post content. On an active blog, this multiplies fast.
- Autosaves: WordPress saves every 60 seconds while editing. A long editing session creates dozens of autosave records.
- Spam comments: Even if you moderate comments, spam sits in the database marked as spam. Akismet catches 99.9 percent of spam, but it still stores them.
- Transient options: Temporary data from plugins that expires but is never deleted. Plugins cache data with expiration times, but cleanup is often forgotten.
- Orphaned post meta: Data linked to deleted posts that lingers. When you delete a post, its metadata sometimes remains behind.
- Unused tables: Plugins that deactivate but leave tables behind. A deactivated e-commerce plugin might leave 10+ tables unused.
- Log entries: Some plugins log every action (page views, failed logins, API calls). Log tables grow exponentially if not cleaned.
Step 1: Delete Post Revisions and Autosaves
Open phpMyAdmin and run these queries one at a time (do NOT run them all at once on your first attempt):
DELETE FROM wp_posts WHERE post_type = 'revision';
DELETE FROM wp_postmeta WHERE post_id NOT IN (SELECT ID FROM wp_posts);
The first deletes all revision copies. The second removes post meta data (settings, custom fields) that belong to deleted posts. After running each query, note how many rows were deleted. This tells you how much data you are removing. On a 5-year-old blog, this could be hundreds of thousands of rows.
Step 2: Clean Spam and Trash Comments
Spam comments still consume space. Delete them permanently:
DELETE FROM wp_comments WHERE comment_approved = 'spam';
To also empty the comment trash (approved = ‘trash’):
DELETE FROM wp_comments WHERE comment_approved = 'trash';
Then clean up orphaned comment meta:
DELETE FROM wp_commentmeta WHERE comment_id NOT IN (SELECT comment_ID FROM wp_comments);
On a site with Akismet enabled, this could delete thousands of spam records that were never manually deleted. Be aware that you cannot restore deleted comments, so only run this if you are certain you do not need them.
Step 3: Remove Expired Transients
Transients are temporary options that plugins use for caching. Many expire but are never cleaned:
DELETE FROM wp_options WHERE option_name LIKE '%_transient_%' AND option_name NOT LIKE '%_transient_timeout_%';
This is safe because transients are meant to be temporary and will be regenerated on demand. Plugins will simply recreate them if they need them. However, this step is optional if your transients are already clean.
Step 4: Delete Unused Plugin Tables
When you deactivate a plugin, it may leave tables behind. Check the wp_ prefix for tables that no longer serve active plugins:
SHOW TABLES LIKE 'wp_%';
Review the list and drop tables that you no longer need. For example, if you deactivated a testimonials plugin:
DROP TABLE wp_testimonials;
Be careful here. Only drop tables if you are certain they are orphaned. When in doubt, keep them. One way to verify is to check your active plugins list in WordPress and see if any plugin owns the table. Some plugins document their tables in their admin pages.
Step 5: Optimize Database Tables
After deleting data, tables contain empty space. Optimize them to reclaim disk:
OPTIMIZE TABLE wp_posts, wp_postmeta, wp_comments, wp_commentmeta, wp_options;
This repacks the table data and reduces file size. Repeat for all tables you modified. Optimization can take time on very large tables, so run it during off-peak hours (late night or early morning). Check your hosting control panel to see current traffic patterns.
Step 6: Automate Future Cleanup
Now that your database is clean, keep it lean automatically. ReadyGo Toolkit (free) includes a scheduled cleanup module that removes expired transients, spam comments, and old revisions daily. Set it and forget it. No manual queries needed. The plugin runs cleanup during low-traffic times and logs what it cleaned.
How Much Space Did You Save?
Check your database size before and after cleanup:
- Go to phpMyAdmin > Databases.
- Scroll down and look for your database size in the list.
- Compare the size to what it was before cleanup.
Sites with heavy editing or decades of spam comments often reclaim 50-200 MB. Some sites with plugin logging enabled might reclaim 500 MB or more. The space savings vary widely depending on your site’s activity level.
Verify Your Cleanup Worked
After cleanup, visit your website and check that everything loads normally. Test:
- Homepage loads without errors
- Comments display correctly (if you have comments enabled)
- Search works
- Admin dashboard loads
- Posts can be edited and saved
If something breaks, restore your backup immediately. You made a backup before starting, right?
Frequently Asked Questions
Will cleanup affect my site speed?
Yes, for the better. Smaller tables mean faster queries. Most sites see a 5-10 percent speed improvement after cleanup. Some see up to 20 percent improvement if their database was severely bloated.
Can I recover deleted comments?
No. Once deleted from the database, they are gone. The backup you created at the start is your only safety net. If you need them, restore the backup and skip that cleanup step.
How often should I clean the database?
Once every 3-6 months if you have active editors and comments. Sites that rarely update can clean annually. Heavy-traffic sites might benefit from monthly cleanups. Use the ReadyGo Toolkit automated cleanup to do this daily without manual effort.
Is there a risk of breaking my site?
Low, if you follow the steps carefully. The biggest risks are: deleting the wrong table, or deleting data from an active plugin that still uses it. Back up first and be selective about what you delete. When in doubt, keep it.
Should I optimize tables immediately after cleanup?
Yes. Optimization reclaims disk space that delete operations leave behind. Run optimization on all affected tables to maximize the space savings.
What about InnoDB vs MyISAM tables?
The OPTIMIZE TABLE command works on both, but InnoDB handles space more efficiently. Most modern WordPress installs use InnoDB exclusively, so this is not a concern.