Table of Contents
Your WordPress database grows messy over time. Post revisions, spam comments, expired transients, and orphaned metadata bloat the database, slowing queries and backups. Regular optimization keeps your site lean and fast.
What Bloats the Database?
- Post revisions: WordPress saves a copy every time you edit — dozens per post
- Spam/pending comments: accumulate even with Akismet
- Transients: temporary cache data that expires but isn’t always cleared
- Orphaned metadata: leftover data from deleted posts/plugins
- Expired sessions: old login sessions in the options table
Step 1: Limit Post Revisions
Add to wp-config.php: define('WP_POST_REVISIONS', 5); This keeps only the 5 most recent revisions. For a strict cap, use false to disable revisions entirely (not recommended for collaborative sites).
Step 2: Delete Spam and Trash
In wp-admin, go to Comments → Spam and bulk delete. Set auto-empty trash by adding define('EMPTY_TRASH_DAYS', 7); to wp-config.php — older trash auto-deletes after 7 days.
Step 3: Clean Transients
Transients expire automatically but often linger. The WP-Optimize plugin clears expired transients and offers scheduled cleanups. You can also run DELETE FROM wp_options WHERE option_name LIKE '%transient%' via phpMyAdmin (backup first!).
Step 4: Optimize Tables
Over time, MySQL tables fragment. In phpMyAdmin, select all tables and choose Optimize table. WP-Optimize automates this with a scheduled task. Optimization reclaims space and speeds up queries.
Step 5: Use a Maintenance Plugin
WP-Optimize (free) combines database cleanup, image compression, and caching. Schedule weekly automatic cleanups. Advanced Database Cleaner offers deeper orphaned-data removal. Both are safe with backups enabled.
Frequently Asked Questions
Will optimization break my site? Standard cleanup (revisions, spam, transients) is safe. Always backup first, especially before manual SQL. Table optimization is non-destructive.
How often should I optimize? Monthly for active sites, quarterly for static ones. Automated scheduling via WP-Optimize eliminates the need to remember.
Can I do this via command line? Yes — wp db optimize (WP-CLI) optimizes all tables. For cleanup, use wp revision delete --all to bulk-remove revisions.
Conclusion
Database optimization is low-effort, high-impact maintenance. Install WP-Optimize, limit revisions, schedule monthly cleanups, and optimize tables quarterly. Your queries get faster, backups get smaller, and your site stays healthy.
