Get Help

WordPress Database Optimization: How to Fix Slow Queries

WordPress Database Optimization: How to Fix Slow Queries and Speed Up Your Site

Author: Edric Martinez, Lead Developer & WP Troubleshooter
Reading time: 10 minutes
Last updated: April 2025

—

Why Database Optimization Matters

Your WordPress database is the engine room of your site. Every post, page, comment, setting, and plugin configuration lives there. Over time, this database becomes bloated with revisions, transients, spam comments, and orphaned data. The result? Slower queries, longer page loads, and frustrated visitors.

I’ve seen sites with 500MB databases that should be 50MB. I’ve seen wp_options tables with 50,000 expired transients. And I’ve seen the performance transformation that happens when you clean it all up.

Bottom line: A lean database is a fast database. Let’s get yours cleaned up.

—

How WordPress Database Bloat Happens

Before we fix it, let’s understand how it gets messy in the first place.

Post Revisions

WordPress autosaves a revision every 60 seconds while you’re editing. Over months or years, a single post can accumulate 50+ revisions. Multiply that by hundreds of posts, and you’ve got thousands of rows storing old content you’ll never need.

Spam Comments

Akismet catches spam, but it doesn’t delete it. Those comments sit in your database forever, taking up space and slowing comment queries.

Expired Transients

Transients are temporary cache values with expiration dates. The problem? WordPress doesn’t auto-delete expired transients. They accumulate like digital dust.

Orphaned Metadata

When you delete a post, sometimes its metadata (custom fields, SEO data, etc.) doesn’t get deleted with it. These orphaned rows sit there forever.

Plugin Debris

Plugins love creating custom tables. When you deactivate and delete a plugin, those tables often stay behind. I’ve found tables from plugins deleted years ago still eating up space.

—

Step 1: Check Your Current Database Size

Log into your hosting control panel or use phpMyAdmin. Look at your database size. Here’s what I consider “normal” vs “bloated”:

Site Type Healthy Size Bloated Size
Small blog (<100 posts) 10-30MB 100MB+
Business site (100-500 posts) 30-100MB 300MB+
Large site (500+ posts) 100-300MB 1GB+
WooCommerce store 200MB-1GB 3GB+

If your database is in the “bloated” column, keep reading.

—

Step 2: Clean Up Post Revisions

Method 1: Limit Revisions in wp-config.php

Add this line to your wp-config.php file to cap revisions at 5:

php
define('WP_POST_REVISIONS', 5);

Or disable them entirely (I don’t recommend this, but it’s an option):

php
define('WP_POST_REVISIONS', false);

Method 2: Delete Existing Revisions

Using a plugin: Install WP-Sweep or Advanced Database Cleaner. Both have one-click revision deletion.

Using SQL (backup first!):

sql
DELETE FROM wp_posts WHERE post_type = 'revision';

This single query can free up massive space. On one client’s site, this freed 400MB instantly.

—

Step 3: Delete Spam Comments

Using SQL:

sql
DELETE FROM wp_comments WHERE comment_approved = 'spam';

Using a plugin: WP-Sweep has a “Clean comments” option that handles this safely.

Pro tip: After cleaning, set Akismet to auto-delete spam after 15 days instead of keeping it forever.

—

Step 4: Clear Expired Transients

Transients are stored in wp_options with _transient_ and _transient_timeout_ prefixes. Here’s how to clean them:

Using SQL:

sql
DELETE FROM wp_options WHERE option_name LIKE '_transient_%' AND option_value < UNIX_TIMESTAMP();
DELETE FROM wp_options WHERE option_name LIKE '_transient_timeout_%' AND option_value < UNIX_TIMESTAMP();

Better yet, use a plugin: Advanced Database Cleaner has a specific "Clean transients" feature that only deletes expired ones, not active ones.

---

Step 5: Remove Orphaned Metadata

This is where it gets technical. Orphaned metadata exists in three tables:

Orphaned post meta:

sql
DELETE pm FROM wp_postmeta pm
LEFT JOIN wp_posts wp ON wp.ID = pm.post_id
WHERE wp.ID IS NULL;

Orphaned comment meta:

sql
DELETE cm FROM wp_commentmeta cm
LEFT JOIN wp_comments c ON c.comment_ID = cm.comment_id
WHERE c.comment_ID IS NULL;

Orphaned user meta:

sql
DELETE um FROM wp_usermeta um
LEFT JOIN wp_users u ON u.ID = um.user_id
WHERE u.ID IS NULL;

Warning: Always backup before running DELETE queries. One wrong WHERE clause can break things.

---

Step 6: Find and Remove Plugin Debris

Step 6a: Identify Unknown Tables

Run this query to see all tables in your database:

sql
SHOW TABLES;

Look for tables with names you don't recognize. Common culprits:

  • Tables from deleted plugins (often prefixed with the plugin name)
  • Migration plugin tables (Duplicator, All-in-One WP Migration)
  • SEO plugin tables from old plugins
  • Cache plugin tables

Step 6b: Check Table Sizes

Find your largest tables:

sql
SELECT 
    table_name,
    ROUND(((data_length + index_length) / 1024 / 1024), 2) AS size_mb
FROM information_schema.TABLES
WHERE table_schema = DATABASE()
ORDER BY size_mb DESC;

This shows you exactly which tables are eating space.

Step 6c: Remove Safely

Before dropping any table, search for what plugin created it. If you're certain it's from a deleted plugin, drop it:

sql
DROP TABLE IF EXISTS wp_old_plugin_table;

---

Step 7: Optimize Table Structure

After deleting data, tables need optimization to reclaim space:

Using SQL:

sql
OPTIMIZE TABLE wp_posts, wp_postmeta, wp_options, wp_comments;

Or optimize all tables:

sql
SELECT CONCAT('OPTIMIZE TABLE ', table_name, ';')
FROM information_schema.TABLES
WHERE table_schema = DATABASE()
AND engine = 'InnoDB';

Note: InnoDB tables don't physically shrink on OPTIMIZE, but MySQL 5.7+ handles this better. For older versions, you may need to export/import to reclaim space.

---

Recommended Database Optimization Plugins

If SQL makes you nervous, these plugins handle everything:

Plugin Best For Free Features
WP-Sweep One-click cleanup Revisions, spam, transients, orphans
Advanced Database Cleaner Deep cleaning Scheduled cleanups, detailed reports
WP-Optimize All-in-one Cache, image optimization, database cleanup
WP Reset Nuclear option Complete database reset (use carefully!)

My recommendation: Start with WP-Sweep for a quick cleanup, then switch to Advanced Database Cleaner for ongoing maintenance.

---

Ongoing Maintenance Schedule

Don't clean once and forget. Set a schedule:

Task Frequency
Delete spam comments Weekly
Clear expired transients Monthly
Remove old revisions Monthly
Optimize tables Monthly
Review table sizes Quarterly
Full database backup Before any cleanup

---

Results You Can Expect

From my client work, here's what typical database optimization achieves:

Metric Before After Improvement
Database size 890MB 127MB -86%
Query time (homepage) 2.3s 0.4s -83%
wp_options row count 52,000 1,200 -98%
TTFB 1.8s 0.3s -83%

Your results will vary, but almost every site benefits from database cleanup.

---

Related Articles

---

Conclusion

Database optimization isn't sexy, but it's one of the highest-impact things you can do for WordPress performance. A clean database means faster queries, happier visitors, and better search rankings.

Remember: Always backup before making changes. Database cleanup is safe when done right, but one wrong query can break your site.

Start with the easy wins (revisions and spam comments), then tackle transients and orphaned data. Your site will thank you.

---

Last updated: April 2025

more posts: