Database Optimization: The Single Biggest Speed Win

Let me push back on the headline before I defend it. Database optimization is not the biggest speed win on a brand-new WordPress site. On a fresh install with ten posts, your bottleneck is unoptimized images and a missing page cache, not your database. But on a mature site — one that has run for three or four years, churned through a dozen plugins, and stored every Elementor revision since 2022 — the database is almost always the single biggest overlooked win. It is the speed problem nobody profiles because it hides behind a green-ish PageSpeed score while quietly adding 300ms to every single request.

Here is where that time actually goes, and how to claw it back.

The real headline: wp_options autoload bloat

WordPress loads a chunk of the wp_options table into memory on every page request, before it has decided what page it's even rendering. These are the rows flagged autoload = 'yes'. Themes and plugins dump settings here, and badly behaved ones never clean up after deactivation. The result is an autoload payload that quietly grows from a healthy 200KB to several megabytes of serialized junk that has to be unserialized on every uncached hit.

You can measure it in one query. From phpMyAdmin, Adminer, or wp db query:

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

A healthy site sits comfortably under 1MB. If you're seeing 3MB or 5MB, you've found your problem. To see who the culprits are:

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

You'll usually find a few offenders: an analytics plugin caching giant blobs, a slider plugin storing every slide as autoloaded data, or orphaned rows from a theme you removed last year. WordPress 6.6 made this less dangerous by introducing a size threshold above which large new options are no longer autoloaded automatically, and by switching the column to explicit 'on'/'off'/'auto' values. That helps going forward, but it does not retroactively fix the bloat an old plugin already left behind. You still have to clean house.

Flipping autoload off safely

For a confirmed-junk row from a deactivated plugin, you can stop it autoloading without deleting it:

• UPDATE wp_options SET autoload = 'no' WHERE option_name = 'the_offending_option';

Don't guess. If the row belongs to an active plugin, flipping it can break that plugin's caching logic. Verify the source first — a quick search of the option name almost always tells you which plugin owns it.

What accumulates, and why it matters

Autoload is the headline, but several tables silently fatten over time, and each one slows down the queries that touch it.

• Post revisions. By default WordPress keeps unlimited revisions. A page edited 80 times stores 80 full copies in wp_posts, each dragging along its own wp_postmeta rows. Cap them in wp-config.php: define( 'WP_POST_REVISIONS', 5 ); — five is plenty for recovery without hoarding.
• Expired transients. Transients are meant to expire, but the cleanup is lazy. Sites without a persistent object cache can accumulate tens of thousands of stale _transient_ and _transient_timeout_ rows in wp_options.
• Orphaned postmeta. This is the page-builder tax. Elementor and WPBakery write enormous amounts of layout data into wp_postmeta, and deleting a post doesn't always reap its meta. On heavy builder sites, wp_postmeta is frequently the largest table in the database by a wide margin.
• Auto-drafts and trashed content. Every "Add New" you abandon leaves an auto-draft row behind.
• Spam comments. Tens of thousands of spam rows bloat wp_comments and wp_commentmeta and slow comment counts.

The underused win: missing indexes

Cleaning rows shrinks tables. Adding indexes makes the rows you keep fast to find. WordPress core ships sensible indexes, but its schema was designed for a different era, and the wp_postmeta table in particular punishes the meta queries that WooCommerce and ACF lean on heavily.

The fix most people never reach for is Index WP MySQL For Speed (the OllieJones / Rick James plugin). It adds carefully chosen composite keys to wp_postmeta, wp_usermeta, wp_termmeta, and others, and on a query-heavy WooCommerce store the difference on filtered product and meta lookups is genuinely dramatic. It also measures your real query load first so you can see the before-and-after rather than taking it on faith.

Cut query volume with a persistent object cache

Even a lean, well-indexed database is slow if WordPress asks it the same questions hundreds of times per page. A persistent object cache — Redis or Memcached — stores the results of expensive queries in memory and serves them without touching MySQL again. This is the recommendation behind the "You should use a persistent object cache" notice in Site Health, and it's not cosmetic: on a busy site it can cut database round-trips per request by an order of magnitude.

Managed hosts make this easy. Kinsta and Cloudways offer Redis as an add-on; WP Engine and SiteGround have their own object-cache layers. On a host with Redis available, the Redis Object Cache plugin wires it up in a couple of clicks. Don't confuse this with page caching — page cache serves whole HTML files to anonymous visitors, while object cache speeds up logged-in users, WooCommerce carts, and the admin, which page cache can't touch.

Doing it without breaking the site

Direct SQL is precise but unforgiving. For most people, the safe path is a cleanup plugin plus a profiler.

• Profile first with Query Monitor. It shows you the slowest queries on any page, which plugin fired them, and how many duplicate queries are running. This tells you whether your problem is bloat, missing indexes, or one badly written plugin hammering the database.
• Clean with WP-Optimize, Advanced Database Cleaner, or Perfmatters' database tools. All three remove revisions, expired transients, trashed posts, spam, and orphaned meta from a checklist UI. Schedule a monthly cleanup rather than doing it once and forgetting.
• Mind your storage engine. Modern WordPress uses InnoDB, where the old OPTIMIZE TABLE doesn't "defragment" the way it did on MyISAM — it rebuilds the table and reclaims space, which is useful after a big purge but briefly locks the table. Run it during low traffic.

And the non-negotiable: take a database backup before any purge. Cleanup plugins delete real rows, and "remove orphaned metadata" is exactly the kind of operation that occasionally removes something a poorly-coded plugin still needed. A snapshot you can restore in 90 seconds turns a scary operation into a routine one.

How to know it worked

Don't trust your gut. Measure the metrics database work actually moves: server response time (TTFB) on uncached requests, and the query count and query time that Query Monitor reports. A well-tuned mature site should serve uncached pages with a TTFB comfortably under 600ms, and that improvement flows straight into your Largest Contentful Paint — the Core Web Vital you want under 2.5 seconds. PageSpeed Insights won't always credit the work directly, because it tests a cached page, but your logged-in editors, your checkout, and your admin will feel it immediately. That's the tell that you fixed a real bottleneck and not a cosmetic one.