WordPress Performance: Database optimisation techniques

Diagnosing slow WordPress queries, adding safe indexes, taming wp_options autoload, cleaning orphaned data and scaling MySQL or MariaDB.

Updated 7 min read

A slow WordPress database usually comes down to a few things: meta queries without the right index, a bloated wp_options autoload, and data that old plugins left behind. Heavy plugins, WooCommerce and ACF-driven meta queries make all three worse.

This guide covers how to find the slow queries, then the fixes that make the most difference.

What this covers

  • how to find the queries that are slow, rather than guess
  • which WordPress tables tend to hurt first (and why)
  • how to add indexes safely and validate improvements
  • how to reduce wp_options autoload overhead
  • why the MySQL query cache is no help

1. Find the slow queries first

Measure before you change anything, so you know which change helped.

Use Query Monitor on a staging copy

Query Monitor is still the quickest way to see:

  • slow SQL queries
  • duplicated queries
  • HTTP API calls slowing requests
  • hooks and templates involved

You can also use New Relic APM if you have it, which is often better at showing time spent in MySQL versus PHP.

Turn on the slow query log, on staging

On a staging environment, enable MySQL slow query logging:

SET global slow_query_log = 1;
SET global long_query_time = 1;

Then profile the pages that feel slow and identify patterns: repeated meta queries, unindexed filters, expensive ORDER BY, and queries that read huge numbers of rows.

2. The tables that slow down first

Most performance problems show up in a handful of tables:

  • wp_postmeta (meta queries, ACF fields, WooCommerce)
  • wp_options (autoload bloat, plugin settings, transients without Redis)
  • wp_terms, wp_term_taxonomy, wp_term_relationships (taxonomy-heavy filtering)
  • wp_wc_order_stats and related tables (WooCommerce, depending on version and setup)

If you filter products or posts by several meta fields, wp_postmeta is usually where the time goes.

3. Add the indexes a query needs

An index can turn a slow query into a fast one. Added without a reason, it slows writes and makes tables bigger.

Check what WordPress already indexes

Guides often tell you to add an index on meta_key. WordPress already creates one, along with an index on post_id, so that advice only adds a duplicate that slows writes.

The gap is usually the value. A query that filters on a meta key and its value, such as “products where _stock_status is instock”, can only use the key index and then has to read every matching row. A composite index covers both:

ALTER TABLE wp_postmeta ADD INDEX meta_key_value (meta_key(191), meta_value(32));

Test it on a copy first, and confirm with EXPLAIN that MySQL actually uses it.

The workflow:

  1. identify the slow query
  2. inspect the query plan (EXPLAIN)
  3. add the smallest index that supports that query shape
  4. re-test and confirm the improvement

WooCommerce and filtering

WooCommerce product filtering can generate expensive meta and taxonomy joins. The right fix is often:

  • fewer meta queries
  • better indexing for your specific filter patterns
  • caching (page cache where safe, and Redis object caching for dynamic queries)

Don’t index everything, or writes slow down and storage grows.

4. Clean up orphaned and expired data

Plugins come and go, and many leave their data behind. As well as taking disk space, it can slow queries.

The usual leftovers:

  • orphaned postmeta rows after deleting posts
  • expired transients (especially on sites without Redis)
  • old post revisions
  • unused terms and term relationships

An example clean-up query. Take a backup first:

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

Run it on staging first and confirm nothing breaks. Then repeat it on production, with a way to roll back.

5. Shrink the autoloaded options

Autoloaded options load on every request, so a large autoload adds to time to first byte on every page.

Measure autoload size

Since WordPress 6.6, the autoload column holds more than yes and no. Options can be on, off, auto, auto-on or auto-off, so a query that only checks for yes misses most of them. Use the same list WordPress uses:

SELECT SUM(LENGTH(option_value)) AS autoload_size
FROM wp_options
WHERE autoload IN ('yes', 'on', 'auto-on', 'auto');

Site Health also warns when autoloaded options get too large.

Find the biggest options

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

Then ask:

  • is this option needed on every request?
  • is it a cache that should be a transient?
  • is it left behind by an old plugin?
  • can the plugin be configured to avoid autoload?

The usual fix is to stop storing large, rarely used data in autoloaded options.

6. The MySQL query cache

MySQL 8.0 removed the query cache.

Even before then, it scaled badly with many connections at once. Fast WordPress sites rely on:

  • page caching (HTML)
  • persistent object caching (Redis)
  • application caching (transients and object cache API)
  • database tuning and indexing

7. Storage engine and core database tuning

Use InnoDB

Most WordPress hosts use InnoDB. Convert any table that still uses MyISAM:

ALTER TABLE wp_posts ENGINE=InnoDB;

Buffer pool sizing

The InnoDB buffer pool caches data and indexes in memory. The right value depends on the server and what else is running, but too small a pool sends reads to disk and causes I/O spikes.

If you control the server, set this in my.cnf and test:

innodb_buffer_pool_size=512M

Don’t tune production by guesswork. Watch memory, I/O wait and the database’s own metrics.

8. Query mistakes in custom code

Two fixes that help almost every time:

Select only the columns you need

global $wpdb;

$results = $wpdb->get_results(
    "SELECT ID, post_title
     FROM $wpdb->posts
     WHERE post_type = 'page'
     AND post_status = 'publish'"
);

Don’t use meta_query for everything

Meta queries are convenient, but they get expensive at scale. If you’re building something app-like on WordPress, such as filters, dashboards or directories, you may outgrow wp_postmeta.

At that point, consider:

  • custom tables for high-volume structured data
  • a separate search layer (for example OpenSearch) for complex filtering
  • caching computed views

FAQ

Should I optimise the database every week?

Routine clean-up can help, but most of the gain comes from fixing the causes: autoload bloat, slow queries and missing indexes. If you schedule clean-up, take a backup before each run.

Is MariaDB better than MySQL for WordPress?

Both run WordPress well. Your hosting, configuration and queries make more difference than the choice between them.

When should I move data to custom tables?

When meta queries and joins are the bottleneck, and you have a lot of structured data that WordPress wasn’t designed to query.

Autoloaded options and orphaned data build up again as plugins come and go. Checking for both is part of how I look after WordPress sites.

Published , updated .

Part 2 of WordPress Performance.

Related posts