A slow WordPress site often traces back to one hidden cause: database bloat. Over time, your database fills with unnecessary data. This includes post revisions, spam comments, transients, and trashed items. A proper WordPress database optimization guide helps you clean these tables and restore speed. A lean database means faster queries, quicker page loads, and a better experience for visitors.
Every action you take in WordPress adds rows to your database. Writing a post creates a revision. Receiving a comment stores metadata. Installing a plugin saves options. All this data accumulates. Soon, your database grows from a few megabytes to hundreds of megabytes, or even gigabytes. This bloat slows down every query your site makes. A bloated database also increases backup times and server resource usage.
Database optimization is not just about deleting data. It is about maintaining a healthy site structure. Regular cleanup reduces load on your MySQL server. It also makes backups faster and smaller. So if you want a faster WordPress site, start with the database. This guide walks you through each step with WP-CLI and MySQL commands. You can follow along even if you have basic technical skills.
Many site owners ignore database maintenance until problems arise. By then the database is already bloated and slow. Proactive cleanup prevents these issues before they start. A clean database supports better caching, faster admin screens, and improved query performance across your entire site. This is why every site owner needs a WordPress database optimization guide they can follow regularly.
Why Database Bloat Happens
WordPress stores many things by default. Post revisions are the biggest culprit. Each time you save a draft, WordPress creates a new revision. A post with 50 revisions takes up 50 times more space. These revisions live in the wp_posts table alongside your actual content. They also appear in wp_postmeta with associated metadata entries.
Spam comments also pile up quickly. Automated bots submit thousands of spam comments daily. WordPress holds them in the wp_comments table. Even with anti-spam plugins, some spam slips through. Over months, this adds significant weight to your database. Each spam comment also creates metadata entries that remain after deletion.
Transients are another source of bloat. Plugins and themes use transients to cache data temporarily. Many transients expire but never get deleted. So they sit in your wp_options table forever. Pingbacks and trackbacks also create junk data. They link back to your site from other sites but rarely provide value. A comprehensive WordPress database optimization guide addresses all these problem areas with practical commands.
Post Revisions Cleanup
Post revisions store every change you make to a post. WordPress keeps these in the wp_posts table with post_type set to “revision”. A single page with 100 revisions can use over 10 MB of space. Multiply that by dozens of pages and you see the problem. Cleaning revisions is the fastest way to shrink your database. It also reduces backup size significantly.
Use WP-CLI to delete all revisions at once. This command is powerful and fast. Run it from your server terminal or SSH session. First, navigate to your WordPress root directory.
wp post list --post_type=revision --format=ids | xargs wp post delete --force
This command lists all revision IDs and deletes them permanently. The –force flag skips the trash so they do not come back. First, run the command on a staging site to test it. Then run it on production. Deleting thousands of revisions can take a few minutes. Be patient and let the command finish completely.
You can also delete revisions directly with MySQL. Connect to your database and run this query. It removes every row where post_type equals revision:
DELETE FROM wp_posts WHERE post_type = 'revision';
After deleting revisions, you should prevent future bloat. Add this constant to your wp-config.php file. It limits the number of revisions per post:
define('WP_POST_REVISIONS', 5);This setting keeps only the five most recent revisions. Older ones delete automatically. You can read more about this constant on the WordPress developer documentation. Setting this value prevents revision bloat from ever returning. It works silently in the background after every post save.
Spam Comments and Trash Cleanup
Spam comments take up space in your wp_comments table. They also bloat the wp_commentmeta table. Deleting them regularly keeps your database lean. WP-CLI makes this easy with a single command. Run this from your WordPress directory:
wp comment delete $(wp comment list --status=spam --format=ids)
This command finds all spam comments and deletes them. It removes the comment text, metadata, and related data. Run this command weekly if you get many spam comments. You can also use the MySQL alternative if WP-CLI is not available:
DELETE FROM wp_comments WHERE comment_approved = 'spam';
Emptying the trash is another important step. WordPress keeps trashed posts, pages, and comments for 30 days by default. These items still take up space. You can reduce this time or empty the trash manually after cleanup. Check your Settings > Discussion page for comment moderation options. Set strict spam filters to reduce future spam volume before it accumulates.
Consider using the MySQL query to clean orphaned comment metadata too. This data remains even after you delete the parent comment:
DELETE cm FROM wp_commentmeta cm LEFT JOIN wp_comments c ON c.comment_ID = cm.comment_id WHERE c.comment_ID IS NULL;
Run these cleanup steps monthly for the best results. A clean comments table helps your site stay fast and responsive. It also reduces the time MySQL needs to scan this table during queries.
Transients Cleanup
Transients are temporary data stored in the wp_options table. Plugins use them to cache API responses, widget data, and other short-term information. Each transient has an expiration time. But many expire and stay in the database forever. Expired transients waste space and slow down option queries. They also make backups larger than necessary.
WP-CLI has a built-in command for transients cleanup. Run this to delete all expired transients from your site:
wp transient delete --expired
For multisite networks, add the –network flag to clean every site at once:
wp transient delete --expired --network
You can also clean transients with MySQL. This query deletes expired transients by checking their timeout values stored in the options table:
DELETE FROM wp_options WHERE option_name LIKE '%_transient_%' AND option_value < UNIX_TIMESTAMP();
Some transients never expire because they have no expiration timestamp. These are called orphaned transients. You can find them with this query:
SELECT * FROM wp_options WHERE option_name LIKE '%_transient_%' AND option_name NOT LIKE '%_transient_timeout_%';
Review the results carefully before deleting. Some non-expiring transients may be active. Look for plugin-specific transients that may still hold useful data. Once you confirm they are safe to remove, run a DELETE instead of SELECT. Clean transients help your options table stay small and your site stay fast. This is an often overlooked step in any WordPress database optimization guide.
Database Table Optimization
After removing data, you need to reclaim the space. Database tables do not shrink automatically. Deleting rows leaves gaps in the physical storage. The MySQL OPTIMIZE TABLE command reorganizes the data. It compacts the table and reclaims unused space. This command works well on both InnoDB and MyISAM table engines.
Run the OPTIMIZE TABLE command on your largest tables. First, identify which tables need attention with this query:
SELECT TABLE_NAME, ROUND(((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024), 2) AS 'Size (MB)' FROM information_schema.TABLES WHERE TABLE_SCHEMA = 'your_database_name' ORDER BY (DATA_LENGTH + INDEX_LENGTH) DESC LIMIT 10;
Then optimize the tables that are largest. Focus on wp_posts, wp_options, wp_postmeta, and wp_comments first. Run these commands one at a time:
OPTIMIZE TABLE wp_posts; OPTIMIZE TABLE wp_options; OPTIMIZE TABLE wp_comments; OPTIMIZE TABLE wp_postmeta;
WP-CLI provides a simpler shortcut. Run this command to optimize all tables at once without writing individual SQL statements:
wp db optimize
This command runs OPTIMIZE TABLE on every WordPress table. It is safe and does not lock tables for long. For InnoDB tables, MySQL rebuilds the table and updates index statistics. For MyISAM tables, it defragments the data file. You can also use phpMyAdmin for a visual approach. Select your database, check all tables, and choose “Optimize table” from the dropdown menu. This method is great if you prefer a GUI or do not have command-line access.
Run table optimization after every major cleanup. It completes the optimization cycle and gives you the maximum performance benefit. Your database will run faster and queries will execute more efficiently on compacted tables.
Automatic Cleanup with wp-config Settings
Manual cleanup works, but automation is better. You can set WordPress to manage database bloat automatically. Add these constants to your wp-config.php file. They reduce future bloat without any manual work. These settings take effect immediately after you save the file.
The WP_POST_REVISIONS constant limits how many revisions each post keeps. Add this line above the “That’s all, stop editing” comment:
define('WP_POST_REVISIONS', 5);Set this to 3 or 5 depending on your workflow. If you never need old versions, set it to 0 to disable revisions entirely. But 3 to 5 is a good balance for most sites. WordPress developer docs explain all the details. Test your preferred value on a staging site first.
The EMPTY_TRASH_DAYS constant controls how long items stay in the trash:
define('EMPTY_TRASH_DAYS', 7);This empties the trash every 7 days instead of the default 30. You can set it to 3 or 5 for even faster cleanup. Set it to 0 to disable the trash entirely. Deleted items go away immediately. Be careful with this setting because you cannot recover items after deletion. It is best to keep it above 0 for safety.
Transients also need automatic cleanup. WordPress cron handles this if you have a proper cron setup. The wp_scheduled_delete hook runs daily. It clears expired transients and other stale data. Check your wp-config.php for DISABLE_WP_CRON. If you use server-level cron, make sure wp-cron.php runs at least hourly. This ensures transients do not pile up between manual cleanups. For other tips, check out our guide on improving WordPress performance for more ways to speed up your site.
Monitoring Database Size
You cannot optimize what you do not measure. Monitoring database size helps you catch bloat early. WP-CLI gives you the total size instantly with a simple command:
wp db size
This command shows the total database size in a readable format. Run it weekly to track growth trends. You can also use MySQL to find the largest tables in your database:
SELECT TABLE_NAME AS 'Table', ROUND(((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024), 2) AS 'Size MB', TABLE_ROWS AS 'Rows' FROM information_schema.TABLES WHERE TABLE_SCHEMA = 'your_database_name' ORDER BY (DATA_LENGTH + INDEX_LENGTH) DESC LIMIT 10;
Replace ‘your_database_name’ with your actual database name. The results show which tables need attention. wp_posts, wp_options, wp_postmeta, and wp_comments are usually the largest. Track these tables over time to spot unusual growth patterns before they become a problem.
Set up a monitoring schedule. Run a quick check every week. Use wp db size to get the total. If the size jumps by more than 10%, investigate the cause. A sudden spike often means a plugin is storing too much data or spam comments are flooding in. Schedule full cleanup and optimization monthly. Put it on your calendar or set up a cron job. Consistent maintenance keeps your database from growing out of control. The official WP-CLI database command documentation provides more options for monitoring and maintenance.
The Bottom Line
Database optimization is a core part of WordPress maintenance. Cleaning post revisions, spam comments, transients, and trashed items frees up significant space. Running OPTIMIZE TABLE after cleanup reclaims unused storage. Setting wp-config constants prevents future bloat from building up again. Each step builds on the last for maximum results.
Start with the WP-CLI commands in this guide. Delete revisions, remove spam, clear transients, and optimize your tables. Then set up automatic limits with WP_POST_REVISIONS and EMPTY_TRASH_DAYS. Monitor your database size weekly with wp db size. A clean database means faster page loads, smaller backups, and a healthier site overall. Make database optimization a regular part of your maintenance routine. Read our WordPress performance articles for more tips on keeping your site fast and efficient.