WordPress & WooCommerce Database Performance Optimisation

Most WordPress performance problems aren't caused by slow hosting or bloated themes. They start in the database. A site that loaded in under a second six months ago now takes four seconds — and nobody changed anything obvious. The culprit is almost always the database: tables growing unchecked, queries running without indexes, and WordPress loading megabytes of data it doesn't need on every page load.

I'm Michal, a UK-based WordPress developer at Rootscope. I've spent 14+ years working with WordPress databases, and I currently manage 70+ sites across multiple servers. Database performance optimisation is one of the most common problems I solve — and one of the most impactful. The difference between a properly tuned database and a neglected one is often the difference between a site that loads in 300 milliseconds and one that times out entirely.

Common Database Performance Killers

Three issues cause the vast majority of WordPress database performance problems. If your site is slow and you've already tried caching plugins and CDNs, chances are one of these is the real bottleneck.

wp_options Autoload Bloat

The wp_options table is WordPress's general-purpose settings store. Every plugin, theme, and WordPress itself uses it to persist configuration. The problem is the autoload column — any row marked autoload = yes gets loaded into memory on every single page load, whether it's needed or not.

On a well-maintained site, autoloaded data might total 500KB. On a neglected WooCommerce store, I've seen it exceed 10MB. That's 10MB of data pulled from the database and loaded into PHP memory before WordPress even starts rendering your page. Multiply that by 30 concurrent visitors and you're looking at 300MB of RAM consumed just by options data.

The usual suspects: expired transients that never got cleaned up, plugin settings for plugins that were deactivated months ago, serialised arrays that grow with every cron run, and WooCommerce session data that accumulates indefinitely.

Orphaned Postmeta

WordPress stores post metadata in the wp_postmeta table — one row per meta key per post. A typical WooCommerce product might have 50-100 meta rows. A WooCommerce order, even more. Delete a product or an old order, and those meta rows stay behind. On a store that's been running for years with thousands of orders, the postmeta table can contain millions of orphaned rows referencing posts that no longer exist. Every query that joins against postmeta now has to scan through this dead weight.

Transient Storms

WordPress transients are meant to be temporary cached values. The problem arises when multiple PHP workers try to regenerate the same expired transient simultaneously. Instead of one database write, you get 20, 30, or 50 concurrent INSERT or UPDATE operations all fighting over the same row. I've seen this pattern bring down production servers entirely.

WooCommerce-Specific Database Issues

WooCommerce amplifies every database problem WordPress has and introduces a few unique ones of its own.

Action Scheduler Table Growth

WooCommerce's Action Scheduler processes background jobs — subscription renewals, email sends, webhook deliveries, and more. Every completed action creates rows in wp_actionscheduler_actions and wp_actionscheduler_logs. On an active store processing hundreds of orders daily, these tables can grow to millions of rows within months. Without proper retention policies and indexing, queries against these tables start timing out, and the scheduler itself slows down — creating a backlog that compounds the problem.

Subscription Query Contention

WooCommerce Subscriptions adds substantial query load. Renewal processing, status checks, and payment token lookups all hit the database. On stores with thousands of active subscriptions, renewal batch processing can create lock contention that blocks front-end queries. Customers trying to browse products are stuck waiting for subscription renewal queries to release their locks.

Term Count Race Conditions

This one catches people off guard. WooCommerce recalculates term counts (product categories, tags, attributes) using a transient in wp_options. When multiple requests trigger a recount simultaneously, they all try to UPDATE the same row. I documented a case where this single transient — _transient_wc_term_counts — caused a full database meltdown with 77 concurrent queries running for up to 376 seconds each and MariaDB consuming 1,479% CPU. The site was completely unresponsive for hours.

My Diagnostic Approach

I don't guess at database problems. Every optimisation engagement starts with data.

Slow Query Log Analysis

The MySQL/MariaDB slow query log captures every query that exceeds a configurable time threshold. I enable this with a low threshold (typically 0.5 seconds) and let it run for 24-48 hours during normal traffic. The results tell me exactly which queries are slow, how often they run, and which tables they touch. This eliminates guesswork — I'm fixing the queries that actually cause problems, not the ones I think might.

SHOW PROCESSLIST and Lock Contention

When a site is actively struggling, SHOW FULL PROCESSLIST gives me a real-time view of every query the database is executing. I can see which queries are waiting for locks, which ones have been running for an unreasonable time, and where the bottleneck is. Combined with InnoDB status output, I can identify lock contention patterns — situations where queries are blocking each other in a chain that degrades the entire server.

Table Structure Audits

I inspect table engines (MyISAM vs InnoDB), check for missing or suboptimal indexes, review table sizes and row counts, and identify fragmentation. WordPress's default schema is designed for broad compatibility, not optimal performance. Adding the right indexes — especially on tables like wp_options, wp_postmeta, and WooCommerce's custom tables — can reduce query times by orders of magnitude.

PHP-FPM and Server Context

Database performance doesn't exist in isolation. If your server is running 50 PHP-FPM workers and each one opens a database connection, that's 50 concurrent connections competing for database resources. I always assess the server environment alongside the database. Often, the most effective fix is reducing PHP-FPM workers to a number the server can actually sustain, which in turn reduces database connection pressure. I cover this in detail in my server management services.

Solutions I Implement

Based on the diagnostic findings, I apply targeted fixes. These are the most common solutions, roughly in order of impact:

Redis object caching eliminates the majority of repetitive database reads. Instead of querying wp_options on every page load, WordPress reads from an in-memory Redis store. For WooCommerce stores, this is the single highest-impact change I make. It reduces database queries per page load from 200-500 down to 20-50, and it completely eliminates transient storms because transients are stored in Redis rather than the database.

Index optimisation on WordPress core tables makes an enormous difference. Adding a composite index on wp_options (autoload, option_name) or optimising the wp_postmeta indexes for WooCommerce's query patterns can turn a 5-second query into a 5-millisecond one. These are changes WordPress doesn't ship by default because they optimise for specific workloads.

InnoDB tuning ensures the database engine is configured for your workload. The defaults in most hosting environments are conservative — designed for shared servers with dozens of databases. For a dedicated WordPress database, tuning innodb_buffer_pool_size, innodb_log_file_size, and innodb_flush_method can dramatically improve write performance and reduce lock contention. InnoDB's row-level locking is also critical for WooCommerce — stores still running MyISAM tables experience table-level locks that serialise all writes.

PHP-FPM right-sizing prevents the server from overwhelming the database. I calculate the appropriate pm.max_children value based on available RAM, per-worker memory consumption, and database connection limits. Over-provisioned PHP-FPM is one of the most common causes of database performance emergencies I deal with.

Scheduled maintenance routines keep the database healthy long-term. This includes automated transient cleanup, postmeta orphan removal, Action Scheduler log pruning, and regular OPTIMIZE TABLE operations for fragmented tables.

Results I've Achieved

Numbers matter more than promises. Here are real results from recent database optimisation work:

• WooCommerce store with 376-second queries — after Redis implementation, InnoDB conversion, and PHP-FPM tuning, query times dropped to under 1 second. A 99.7% improvement. MariaDB CPU usage went from 1,479% to normal levels. The full story is in my WooCommerce database performance crisis write-up.

• Agency portfolio site loading in 6+ seconds — wp_options autoloaded data had grown to 8MB. After cleanup and Redis caching, page load dropped to 1.2 seconds.

• WooCommerce store with 4 million postmeta rows — 2.1 million were orphaned. After cleanup and index optimisation, admin page loads improved by 70% and order searches went from 12 seconds to under 1 second.

• High-traffic blog with constant 502 errors — the database was running out of connections because PHP-FPM was spawning 60 workers on a server that could sustain 20. Right-sizing PHP-FPM and adding Redis eliminated the 502s entirely.

These aren't edge cases. Every WordPress site that's been running for more than a year has some degree of database bloat. The question is whether it's causing noticeable problems yet.

Database performance isn't something you fix once and forget. Tables grow, plugins accumulate data, and traffic patterns change. My WooCommerce maintenance plans include ongoing database health monitoring — autoload audits, transient management, and query performance tracking — so problems are caught early, before they become emergencies.

If your WordPress site is slow and you've tried everything else, the database is almost certainly the answer. Get in touch and I'll run a diagnostic to show you exactly what's going on.