A client site landed on our desk last spring with a 2.4 GB database and a hosting bill that had been “upgraded” twice to fix slowness. The upgrades did nothing. Most of that 2.4 GB was a single analytics plugin’s log table that nobody had read since 2021, and it wasn’t the thing making the site slow anyway. The actual problem was 3.8 MB of autoloaded options being unserialized on every single request, including admin-ajax calls and REST hits.

That gap between “the database is big” and “the database is slow” is where most WordPress database optimization advice falls apart. Size and cost are only loosely related. A 900 MB database with a tight wp_options table will outrun a 60 MB database carrying a 5 MB autoload payload every time.

So here’s what actually matters, in the order it matters, with the SQL we run on real sites.

Measure before you delete anything

Every cleanup starts the same way: find out where the weight is. Run this through wp db query or your SQL client of choice.

SELECT table_name,
       ROUND((datalength + indexlength) / 1024 / 1024) AS mb,
       table_rows  -- estimate only on InnoDB, can be off by 40% or more
FROM information_schema.tables
WHERE table_schema = DATABASE()
ORDER BY (datalength + indexlength) DESC
LIMIT 20;

Nine times out of ten the top of that list is wppostmeta, wpactionscheduleractions, wpactionscheduler_logs, or a plugin’s own log table. Note the sizes. Then ignore them for a moment, because the thing costing you TTFB is almost never the biggest table.

Autoloaded options: the bytes you pay for on every request

On bootstrap, WordPress runs one query to pull every option marked for autoload, stores the result in the alloptions cache key, and hands you an array. One query. WordPress 6.4 added an index on the autoload column, so the query itself is fast even on a table with 40,000 rows.

The cost is everything after the query. PHP has to unserialize the payload, hold it in memory for the lifetime of the request, and if you’re running a persistent object cache, pull the entire blob over the network from Redis on every request before it can do anything else. We measured 34 ms of pure unserialize time on that 3.8 MB client site, on PHP 8.2, before a single template file loaded. That’s per request, on every request, forever.

Find your number:

wp eval '$o = wploadalloptions(); printf("%d options, %.1f KB\n", count($o), strlen(serialize($o)) / 1024);'

And the offenders, written to cover both legacy and post-6.6 autoload values:

SELECT option_name,
       ROUND(LENGTH(option_value) / 1024, 1) AS kb,
       autoload
FROM wp_options
WHERE autoload IN ('yes', 'on', 'auto', 'auto-on')
ORDER BY LENGTH(option_value) DESC
LIMIT 30;

What you’ll find, reliably: an abandoned slider plugin storing every slide as serialized PHP in one row, a security plugin keeping a rolling IP blocklist, a theme dumping its entire settings tree including base64 image data, and a dozen orphaned rows from plugins deleted years ago. Themes are repeat offenders here, which is why we build our own options schema as a single small keyed array rather than one row per control. It’s also why a theme like CanvasWP keeping settings compact matters more than it sounds: options weight is a permanent tax, not a one-off cost.

Flipping an option off autoload safely

Do not run UPDATE wp_options SET autoload = 'off' blindly across the table. Some plugins genuinely need their option early and will issue an extra query per request if it isn’t autoloaded, which can be worse. Target the fat ones individually:

$value = getoption( 'bloatedplugin_cache' );

if ( false !== $value ) {
    deleteoption( 'bloatedplugin_cache' );
    // Fourth arg false = do not autoload. Third arg is the deprecated $deprecated.
    addoption( 'bloatedplugin_cache', $value, '', false );
}

Since 6.6, if a plugin calls addoption() without specifying autoload and the value exceeds 150,000 bytes, core now sets it to auto-off for you. That’s a real improvement, adjustable via the wpmaxautoloadedoption_size filter. It doesn’t help with options written before you upgraded, which is most of them.

One caveat worth knowing before you go hunting: any update_option() call invalidates the whole alloptions cache entry. On a busy site with plugins writing options on every page view, you can end up rebuilding and re-transferring a multi-megabyte blob hundreds of times a minute. Trimming autoload weight fixes that symptom too. For the fuller picture of how these layers interact, we wrote up the whole stack in WordPress caching explained.

Transients: a symptom, not a disease

Transients in the database mean one thing: you have no persistent object cache. Install Redis or Memcached with a working drop-in and every settransient() call routes to the object cache instead of wpoptions. The bloat problem disappears because the storage problem disappears.

Until then, two things go wrong. First, transients created with no expiry are autoloaded by default and never expire, so they sit in your autoload payload permanently. Second, the daily deleteexpiredtransients cron event only removes rows whose timeout has actually passed, and it depends on WP-Cron firing. On sites where cron is broken or where a plugin writes thousands of transients an hour, expired rows pile up faster than they’re cleared.

Audit what’s there:

SELECT option_name,
       ROUND(LENGTH(option_value) / 1024, 1) AS kb
FROM wp_options
WHERE optionname LIKE '\transient\_%'
   OR optionname LIKE '\site\transient\%'
ORDER BY LENGTH(option_value) DESC
LIMIT 25;

Clearing expired ones, safely, without deleting live cache entries:

# Deletes only transients whose timeout has passed, plus their paired value rows
wp transient delete --expired

Nuclear option. Fine on a site with a page cache you can warm afterwards.
wp transient delete --all

If a plugin is writing 50,000 transients a day, deleting them is not the fix. Find the plugin. We’ve seen feed importers keyed by URL hash, and a WooCommerce extension caching per-customer shipping rates with no eviction strategy. Both needed a code fix, not a cleanup schedule.

Revisions and the postmeta multiplier

Revisions themselves are cheap. A revision is a row in wpposts with posttype = 'revision', and unless you’re doing badly written queries without a post_type filter, 20,000 of them will barely register.

The damage is in meta. Core doesn’t copy postmeta to revisions, but ACF does, and so do the major page builders. Elementor stores its entire page structure in a single elementordata meta row, and every revision gets a copy. A complex landing page can carry 300 KB to 600 KB of that JSON. Sixty revisions later you’re looking at 30 MB of postmeta for one page.

Find the worst offenders by parent post, not by total count:

SELECT post_parent, COUNT(*) AS revisions
FROM wp_posts
WHERE post_type = 'revision'
GROUP BY post_parent
ORDER BY revisions DESC
LIMIT 20;

Then cap it. A blanket define( 'WPPOSTREVISIONS', 3 ) in wp-config.php works, but it’s blunt. Editors on a news site want deep history on articles; nobody needs 40 revisions of the privacy policy. Use the filter:

addfilter( 'wprevisionstokeep', function ( $num, $post ) {
    // Builder pages carry huge meta payloads, so keep the window tight.
    if ( inarray( $post->posttype, array( 'page', 'elementor_library' ), true ) ) {
        return 3;
    }

    return 15;
}, 10, 2 );

Deleting existing revisions with wp post delete is the correct way because it cleans the associated meta. Batch it, or you’ll exhaust memory:

wp post list --post_type=revision --format=ids | xargs -n 100 wp post delete --force

Take a backup first. Not a “the host has snapshots” backup, an actual verified one. Our reasoning on that is in the 3-2-1 rule applied to real sites.

While you’re in there, clear orphans left behind by years of plugin churn:

DELETE pm FROM wp_postmeta pm
LEFT JOIN wpposts p ON p.ID = pm.postid
WHERE p.ID IS NULL;

The tables nobody thinks to look at

Core’s contribution to bloat is modest. The multi-GB tables are almost always plugin-owned:

• Action Scheduler (wpactionscheduleractions, wpactionschedulerlogs): ships with WooCommerce and dozens of other plugins. It self-prunes completed actions, but the default retention keeps a month, and a store processing tens of thousands of jobs a day will still hold millions of rows. Check WooCommerce, Status, Scheduled Actions for failed and pending counts before deleting anything.
• Yoast indexables (wpyoastindexable, wpyoastindexable_hierarchy): grows with every post, term, author and attachment. Large, but functional. Don’t truncate it casually; rebuilding takes hours on a big site.
• WooCommerce sessions (wpwoocommercesessions): should self-clean daily via cron. If it’s at a million rows, your cron is broken, which is a bigger problem than the table.
• Security and analytics logs: Wordfence, statistics plugins, form plugins storing every submission. These are the ones that hit gigabytes. Decide on a retention period and enforce it, especially if submissions contain personal data, which brings retention obligations into play.

The WordPress database optimization pass, in order

This is the sequence we run on handover audits. It takes about 40 minutes on a typical site.

1. Back up and verify. Restore it somewhere. An unverified backup is a wish.
2. Measure autoload weight. If it’s over 500 KB, this is your whole afternoon and it’s worth it.
3. Kill orphaned options from plugins that no longer exist. Search wp_options for prefixes you don’t recognise, confirm against your active plugin list, then delete.
4. Flip the fat survivors off autoload one at a time, testing the front end and admin after each. Some will break. You want to know which.
5. Clear expired transients and then fix whatever is generating them. Add a persistent object cache if there isn’t one.
6. Cap revisions with the filter, then batch-delete the backlog.
7. Clean orphaned postmeta and term relationships.
8. Re-measure. TTFB before and after, same URL, same conditions, 10 samples. If nothing moved, the database was never your bottleneck and you should go back to a proper diagnostic method.

Reclaiming disk space, and when not to bother

Deleting a million rows from an InnoDB table frees the pages for reuse inside that table’s tablespace. It does not return the space to the filesystem, and it does not necessarily make queries faster, since the index tree keeps its shape. To actually shrink the file you need OPTIMIZE TABLE, which for InnoDB maps to ALTER TABLE ... FORCE: a full table rebuild.

On MySQL 8.0 that’s mostly online, but it still copies the whole table, needs free disk equal to the table size, and on a 4 GB table on shared hosting it can stall writes long enough for visitors to notice. Run it during a maintenance window, on the specific tables you emptied. Never on a schedule across every table.

wp db query "OPTIMIZE TABLE wp_postmeta;"

Be honest about the payoff. On a decent host with SSD storage and room to spare, reclaiming 800 MB buys you nothing measurable. The reason to do it is migration speed and backup size, not page load. Whether any of it helps at all depends heavily on your stack, which is a hosting question as much as a database one.