Database Optimization and Cleanup

Lesson 8 of 14 12 min read
Keyboard shortcuts
  • JNext lesson
  • KPrevious lesson
  • /Search lessons
  • EscClear search

Your WordPress database is like a closet you never clean. Every day, WordPress stuffs more data in there. Post revisions from every save. Transient data from plugins. Spam comments. Orphaned metadata from plugins you deleted years ago. Autoloaded options that grow silently.

On a fresh WordPress install, the database is about 1-2MB. After a year of active use, it’s 50-200MB. After three years with WooCommerce? I’ve seen 500MB+ databases on sites with barely 100 products. And the bigger it gets, the slower every page load becomes. Not by a lot, at first. But it compounds. A 1-2 second overhead on a site that should load in under a second.

I clean up databases on client sites regularly. The record was a WooCommerce site where the wp_options table alone was 47MB. After cleanup, it dropped to 2.4MB. The admin dashboard went from a 6-second load to 1.8 seconds. No other changes.

This chapter shows you exactly where the bloat lives and how to fix it.

What’s in Your Database

WordPress stores everything in a MySQL (or MariaDB) database with about 12 default tables. The important ones for performance:

wp_posts. Your content. Posts, pages, custom post types, and their revisions. Each revision is a full copy of the post.

wp_postmeta. Metadata attached to posts. Custom fields, SEO data, layout settings. Page builder plugins store their layout data here, and it gets massive.

wp_options. Site settings. Theme options, plugin settings, transients, cron schedules. This is the problem table. It’s queried on every single page load.

wp_comments and wp_commentmeta. Comments and their metadata. If you’ve ever had a spam attack, these tables can hold hundreds of thousands of rows.

wp_usermeta. User metadata. On a membership site with 50,000 users, this table gets huge.

wp_termmeta. Term metadata. Usually small unless you’re running a massive product catalog.

The tables that cause the most performance problems are wp_options (autoloaded data), wp_postmeta (sheer size), and wp_posts (revisions). Those are where we focus.

The wp_options Autoload Problem

This is the single biggest database performance issue I see on WordPress sites. It’s also the least understood.

The wp_options table stores key-value pairs. Plugin settings, theme options, site URL, everything WordPress needs to function. Each row has an autoload column set to either “yes” or “no.”

When autoload is “yes,” WordPress loads that option into memory on every single page load. Every page. Every request. Before your theme even starts rendering. The idea is that frequently needed data should be pre-loaded.

The problem? Plugins set autoload = yes on data that doesn’t need it. I’ve seen plugins store 5MB of serialized data with autoload enabled. Every page load has to read that 5MB into memory, parse it, and then ignore it because the current page doesn’t need it.

Finding Your Autoload Size

You can check your autoloaded data size with a SQL query. If you have phpMyAdmin access (most hosts provide it), run this:

SELECT SUM(LENGTH(option_value)) AS autoload_size
FROM wp_options
WHERE autoload = 'yes';

A healthy site has under 500KB of autoloaded data. I’ve seen sites with 5-10MB. That’s 10-20x the data loaded into memory on every request.

To find the specific offenders:

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

This shows you the 20 largest autoloaded options. Common culprits:

Plugin-specific settings that are too large. Some plugins store entire configuration objects with autoload enabled. If you see a plugin name in the option_name with a size over 100KB, that plugin has a problem.

Expired transients. Transients with autoload set to “yes” that haven’t been cleaned up. They pile up.

Old data from deleted plugins. When you delete a plugin, its options stay in the database. Forever. With autoload still set to “yes.”

Fixing Autoloaded Options

For options from plugins you’ve deleted, it’s safe to delete the rows entirely. For options from active plugins, don’t delete them, but you can change autoload from “yes” to “no” for data the plugin doesn’t need on every page load.

Be careful here. Changing autoload on the wrong option can break things. Start with data from deleted plugins. Then move to known safe options. If you’re not comfortable with SQL, use the “Jetstash Autoload” plugin or “Query Monitor” (which shows autoloaded data size in its admin bar).

Post Revisions: The Stealth Storage Hog

Every time you click “Save Draft” or “Update” on a post, WordPress creates a full copy of the content as a revision. Write a 2,000-word article and hit save 15 times while editing? That’s 15 full copies of the article stored in wp_posts, plus metadata for each one in wp_postmeta.

I once audited a blog with 500 published posts. The wp_posts table had 47,000 rows. Of those, 38,000 were revisions. 80% of the content table was duplicate data nobody would ever use.

Limiting Revisions

Add this line to your wp-config.php file:

define( 'WP_POST_REVISIONS', 5 );

This limits WordPress to keeping the 5 most recent revisions per post. Old ones get discarded when the post is saved. You can set it to 3, or even 2. I use 5 because it gives me enough history to recover from mistakes without bloating the database.

You can also disable revisions entirely:

define( 'WP_POST_REVISIONS', false );

I don’t recommend disabling completely. Revisions have saved me more than once when a client accidentally overwrote their own content. Five revisions is the sweet spot.

Cleaning Existing Revisions

The constant only affects future saves. Existing revisions are still in your database. To clean them out:

WP-Optimize plugin. Go to Database > Optimizations > “Clean all post revisions.” One click. It finds and deletes all revisions beyond your set limit (or all of them if you choose).

Manual SQL (backup first):

DELETE FROM wp_posts WHERE post_type = 'revision';

Then clean up the orphaned metadata:

DELETE FROM wp_postmeta WHERE post_id NOT IN (
    SELECT ID FROM wp_posts
);

On that 500-post blog I mentioned, deleting revisions removed 38,000 rows and freed 120MB of database space. The site’s database-heavy pages loaded 15-20% faster.

Transients: The Hidden Accumulator

Transients are WordPress’s built-in caching mechanism. Plugins use them to store temporary data, like API responses, remote resource checks, or feed data. They have an expiration time, and WordPress is supposed to delete them when they expire.

“Supposed to” is the key phrase.

WordPress only deletes expired transients when they’re specifically requested. If a plugin stores a transient and then never checks it again (because you deactivated the plugin, or the feature that uses it changed), the expired transient sits in your database forever.

I’ve seen sites with 10,000+ expired transients. They take up space in wp_options and slow down queries.

Cleaning Transients

WP-Optimize: Database tab > “Clean transient options.” It removes all expired transients with one click.

Manually:

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

Then:

DELETE FROM wp_options
WHERE option_name LIKE '_transient_%'
AND option_name NOT LIKE '_transient_timeout_%'
AND option_name NOT IN (
    SELECT REPLACE(option_name, '_transient_timeout_', '_transient_')
    FROM wp_options
    WHERE option_name LIKE '_transient_timeout_%'
    AND option_value > UNIX_TIMESTAMP()
);

If you’re running Redis for object caching (from Chapter 6), transients are stored in Redis instead of the database. This makes the transient cleanup less important for performance, but you should still clean them periodically to keep your database tidy.

Database Optimization Tools

You don’t need to write SQL queries by hand. These tools handle the heavy lifting.

WP-Optimize (Recommended)

Free plugin. Does everything you need:

  • Removes post revisions
  • Cleans auto-drafts and trashed posts
  • Deletes spam and trashed comments
  • Removes expired transients
  • Cleans orphaned postmeta and commentmeta
  • Optimizes database tables (OPTIMIZE TABLE command)
  • Lets you schedule automatic cleanups

I install WP-Optimize on every client site. Set it to run weekly cleanups automatically. Takes 30 seconds to configure. The scheduled cleanup runs:

  1. Remove post revisions older than 2 weeks
  2. Clean all auto-drafts
  3. Empty trash
  4. Remove spam comments
  5. Clean transients
  6. Optimize tables

Configure this once, forget about it. Your database stays lean without manual effort.

Advanced Database Cleaner

A more detailed option if WP-Optimize doesn’t catch everything. It finds orphaned tables left by deleted plugins, which WP-Optimize doesn’t do. Useful for sites that have been through multiple plugin installs and removals. The free version shows orphan tables; the pro version lets you clean them.

Manual SQL (for the Adventurous)

If you’re comfortable with phpMyAdmin or the MySQL command line, direct SQL gives you the most control. Always, always backup your database before running DELETE queries. I use this approach on large databases where plugins time out because there’s too much data to process in a single PHP request.

Query Optimization: Finding Slow Queries

A clean database is one thing. Efficient queries are another. If a plugin runs a poorly written query, even a lean database will be slow.

Query Monitor: Your Best Friend

Query Monitor is a free WordPress plugin that shows you every database query on every page. Install it on your staging site (not production, it adds overhead), and check:

Total queries per page. A typical WordPress page should run 20-50 queries. If you’re seeing 200+, something is very wrong. I’ve seen sites with 500+ queries per page because of poorly coded themes or plugins running queries in loops.

Slow queries. Query Monitor highlights queries that take more than 0.05 seconds. Sort by time to find the worst offenders. Each slow query tells you which plugin or theme file triggered it.

Duplicate queries. The same query running multiple times per page. This means a plugin is requesting the same data over and over instead of caching the result. Object caching (Redis) helps here, but the real fix is fixing the plugin.

What to Do with Slow Queries

  1. Identify the source plugin/theme using Query Monitor
  2. Check if the plugin has an update that fixes the query
  3. If it’s a theme issue, switch to a lighter theme
  4. If it’s a plugin you can’t replace, add Redis object caching to cache the query results
  5. As a last resort, add a database index for the specific slow query (advanced, only if you know MySQL)

Most slow queries I find come from three sources: page builders (complex layout queries), WooCommerce (product queries with many filters), and poorly coded themes (loading all posts to count them instead of using COUNT queries). Switching to a lighter theme or adding Redis usually solves it.

Scheduled Cleanup: Set It and Forget It

One-time cleanup is good. Automated ongoing cleanup is better. Your database doesn’t stop growing just because you cleaned it once.

WP-Optimize Scheduled Cleanup

Go to Settings > Scheduling. Enable weekly or bi-weekly cleanups. I use:

Weekly: Clean post revisions (keep 5), clean auto-drafts, empty trash, delete spam comments, clean expired transients.

Monthly: Optimize database tables. This is the MySQL OPTIMIZE TABLE command that defragments tables and reclaims space. Don’t run this daily. It locks tables briefly and can cause issues on high-traffic sites.

WP-Cron Consideration

WordPress scheduled tasks run via WP-Cron, which triggers on page visits. If your site gets low traffic, scheduled cleanups might not run when expected. On Cloudways and other managed hosts, you can set up a real server cron job to call wp-cron.php at regular intervals:

*/5 * * * * curl -s https://yoursite.com/wp-cron.php > /dev/null 2>&1

This runs WP-Cron every 5 minutes regardless of traffic. Then disable WordPress’s built-in cron trigger by adding this to wp-config.php:

define( 'DISABLE_WP_CRON', true );

This prevents WP-Cron from firing on every page load (which adds a small overhead) and ensures your scheduled tasks run on time.

MySQL vs. MariaDB: Does It Matter?

MariaDB is a fork of MySQL created by MySQL’s original developer. It’s generally faster for read-heavy workloads, which is exactly what WordPress does. Many hosting providers have switched to MariaDB.

But here’s the honest truth: the difference is small for most WordPress sites. Maybe 5-10% on database-heavy operations. If you’re on a host that runs MariaDB, good. If you’re on MySQL 8.0, also good. Neither one is going to make or break your site performance.

The far bigger wins come from:

  1. Cleaning up the autoloaded options (2-5x improvement on database operations)
  2. Removing revisions and bloat (15-30% improvement)
  3. Adding Redis object caching (3-10x improvement on database query speed)
  4. Fixing slow queries found by Query Monitor (variable, but sometimes 50%+ improvement)

If your host lets you choose, pick MariaDB. If not, don’t lose sleep over it. The optimizations in this chapter will give you 10-50x more improvement than switching database engines.

Where the database engine does matter: if you’re running a WooCommerce store with 50,000+ products and complex filtering, or a membership site with 100,000+ users. At that scale, MariaDB’s performance advantages become measurable. For a blog or a small business site? Irrelevant.

Chapter Checklist

  • [ ] Checked autoloaded data size (target: under 500KB)
  • [ ] Identified and cleaned oversized autoloaded options
  • [ ] Post revisions limited to 5 in wp-config.php
  • [ ] Existing excess revisions deleted
  • [ ] Expired transients cleaned
  • [ ] WP-Optimize installed and configured
  • [ ] Scheduled weekly database cleanup is running
  • [ ] Query Monitor installed on staging to audit queries
  • [ ] Slow queries identified and addressed (plugin update, Redis, or replacement)
  • [ ] WP-Cron set up as a server cron job (not triggered by page loads)
  • [ ] Spam comments and trashed content cleaned
  • [ ] Orphaned meta from deleted plugins removed

Chapter Exercise

Run a database health check right now:

  1. Install WP-Optimize if you haven’t already
  2. Go to the Database tab and note the total items found for each cleanup type
  3. Run all cleanups and record how much data was removed
  4. Open phpMyAdmin and run the autoload size query. Record the total
  5. If over 500KB, run the top-20 autoloaded options query and identify the biggest offenders
  6. Add define( 'WP_POST_REVISIONS', 5 ); to your wp-config.php
  7. Set up weekly scheduled cleanup in WP-Optimize
  8. If you have a staging site, install Query Monitor and check your total query count on the homepage

Write down your autoload size before and after cleanup. Most sites drop by 30-70% on the first cleanup. That directly translates to faster page loads, especially on uncached pages and the admin dashboard.