Optimize Database Performance

Your WordPress database has years of junk in it. Here’s how to clean it out.

This guide helps you get it right the first time.

Query improvement and indexing basics.

Your database is probably full of junk. Let’s clean it out.

Every time someone visits your site, your server queries the database to build the page. The more data in that database, the longer those queries take.

The problem: databases accumulate junk. WordPress saves every revision of every post. Comment spam piles up. Plugins leave data behind when you delete them. Expired cache entries stick around forever.

A WordPress site with 100 posts will have 50,000 database rows. After a few years without maintenance, it will have 500,000. Most of that is garbage.

What’s Slowing Your Database Down

Post Revisions

WordPress saves a new revision every time you hit “Save Draft” or “Update.” Write a 500-word blog post with 20 edits? That’s 20 copies of the same post sitting in your database. Over years, this adds up to thousands of unnecessary rows.

Spam Comments

Even if you delete spam from your dashboard, some plugins leave spam comments in the database marked as “spam” rather than deleting them. Akismet alone can accumulate tens of thousands.

Transients (Expired Cache)

WordPress uses “transients” for temporary data that should expire. Many transients stick around after they’re supposed to die, cluttering your wp_options table.

Orphaned Data

Delete a plugin? Its database tables will stay. Delete a post? Its metadata will remain. These orphaned entries slow down queries without providing any value.

Autoloaded Options

The wp_options table has a field called autoload. Options marked “yes” load on every single page request. Poorly coded plugins stuff megabytes of data in autoloaded options.

The Quick Fix: WP-improve

For WordPress, WP-improve is free and handles most of this automatically.

1. Install WP-improve from the plugin directory
2. Go to WP-improve → Database
3. Review what it found (revisions, spam, transients, etc.)
4. Click “Run all selected optimizations”
5. Schedule weekly cleanup under the Settings tab

That’s it. You’ll probably delete thousands of rows on the first run.

Other Good Options

Choose based on your specific situation.

• WP Rocket (paid): Includes database cleanup plus caching. Worth it if you need both.
• Advanced Database Cleaner (free): More granular control over what gets deleted.
• WP-Sweep (free): Thorough cleanup with good UI.

Manual Database Cleanup (If You Prefer SQL)

Warning: Always back up your database before running DELETE queries. One wrong query can destroy your site.

Find the Bloat

-- See which tables are biggest
SELECT
 table_name AS 'Table',
 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;

Replace your_database_name with your actual database name (check wp-config.php for DB_NAME).

Count the Junk

-- Count post revisions
SELECT COUNT(*) FROM wp_posts WHERE post_type = 'revision';

-- Count spam comments
SELECT COUNT(*) FROM wp_comments WHERE comment_approved = 'spam';

-- Count transients
SELECT COUNT(*) FROM wp_options WHERE option_name LIKE '%transient%';

If any of these return numbers in the thousands or tens of thousands, you’ve found your problem.

Delete the Junk

-- IMPORTANT: Back up your database before running these queries

-- Delete all spam comments
DELETE FROM wp_comments WHERE comment_approved = 'spam';

-- Delete comment metadata for comments that no longer exist
DELETE FROM wp_commentmeta
WHERE comment_id NOT IN (SELECT comment_ID FROM wp_comments);

-- Delete all post revisions (keep zero)
DELETE FROM wp_posts WHERE post_type = 'revision';

-- Or keep the last 3 revisions per post (safer)
-- This is more complex, use WP-improve for this

-- Delete expired transients
DELETE FROM wp_options
WHERE option_name LIKE '%transient_timeout%'
AND option_value < UNIX_TIMESTAMP();

-- Delete orphaned post metadata
DELETE pm FROM wp_postmeta pm
LEFT JOIN wp_posts p ON pm.post_id = p.ID
WHERE p.ID IS NULL;

Optimize Tables After Deletion

After deleting lots of rows, the tables have “holes.” Optimize them:

OPTIMIZE TABLE wp_posts, wp_postmeta, wp_comments, wp_commentmeta, wp_options;

This reclaims disk space and improves query performance.

Fix the Root Causes

Limit Post Revisions

Add to wp-config.php:

// Keep only 3 revisions per post
define('WP_POST_REVISIONS', 3);

// Or disable revisions entirely (not recommended for multi-author sites)
define('WP_POST_REVISIONS', false);

Stop Spam Earlier

Don’t just mark spam—delete it:

• Configure Akismet to delete blatant spam automatically
• Use Antispam Bee for more aggressive filtering
• Consider Cleantalk for high-spam sites

Audit Your Plugins

Some plugins are database hogs:

1. Go to phpMyAdmin
2. Sort tables by size
3. Look for tables with plugin prefixes (not wp_)
4. If a plugin has 50MB of data, ask if that’s reasonable

Common offenders: activity logs, analytics plugins, and anything storing per-user data.

Fix Autoload Bloat

Check what’s autoloading:

SELECT option_name, LENGTH(option_value) AS size
FROM wp_options
WHERE autoload = 'yes'
ORDER BY size DESC
LIMIT 20;

If you see options with sizes in the hundreds of thousands of bytes, that’s a problem. Track down which plugin created them and consider alternatives.

How Often to Optimize

• Weekly: Run WP-improve or similar to clean transients and spam.
• Monthly: Check table sizes and look for abnormal growth.
• Quarterly: Audit autoloaded options and orphaned data.
• After major changes: Deleted a bunch of posts? Removed a plugin? Run optimization.

Non-WordPress Databases

Drupal

Drupal includes cron-based cleanup. Make sure cron is running:

• drush cron for manual run
• Check Status Report for cron status

For manual cleanup, use the DB Maintenance module.

Custom Code / Applications

The principles are the same:

• Identify what’s accumulating (logs, sessions, temp data)
• Set up automated purging
• Add indexes to frequently-queried columns
• Run OPTIMIZE TABLE periodically

Measuring the Impact

Before and after optimization, check:

• TTFB (Time to First Byte): Faster database queries = faster TTFB.
• Page load time: Run PageSpeed Insights or GTmetrix before and after.
• Database size: Note the size in MB before and after cleanup.
• Query Monitor plugin: Shows actual query times on every page load.

Common Database Optimization Mistakes

Avoid these—they cause most of the problems.

• Not backing up first: One bad DELETE query can destroy your site. Always backup.
• Deleting too much: Post revisions are useful if you need to undo changes. Keep at least a few.
• Not scheduling maintenance: One cleanup doesn’t fix the problem. The junk comes back. Schedule regular cleaning.
• Ignoring the source: If spam accumulates daily, fix your spam prevention. If a plugin bloats the database, find an alternative.

The Checklist

Work through each item systematically.

1. Install WP-improve (or similar plugin)
2. Run initial cleanup (revisions, spam, transients, orphaned data)
3. Schedule weekly maintenance
4. Limit revisions in wp-config.php
5. Improve spam prevention (Akismet settings, Antispam Bee)
6. Check after a month to see if the problem is recurring

Sources

• WP-improve Plugin – Free, reliable database cleanup
• WordPress Post Revisions – Official documentation
• Query Monitor Plugin – Debug database performance

Checking Database Health Metrics

• Query Monitor shows no queries taking longer than 1 second
• Database size is stable (not growing excessively month over month)
• wp_options autoload data is under 1MB
• Post revisions are limited and old revisions cleaned up
• TTFB (time to first byte) is under 600ms on hosting dashboard

Ongoing: Run WP-Optimize or similar monthly to prevent bloat accumulation. Set a calendar reminder.