WordPress Performance: Database Optimisation Techniques

A practical database performance guide for WordPress: diagnosing slow queries, adding safe indexes, taming wp_options autoload, cleaning orphaned data, and scaling MySQL or MariaDB.

The WordPress database is the heartbeat of your site. Left unchecked, it becomes bloated, slow, and fragile, especially once you introduce heavy plugins, WooCommerce, and ACF-driven meta queries.

This guide focuses on the small set of database practices that consistently move the needle.

Why the Database Is Usually the Bottleneck

WordPress performance problems often surface as “slow pages”, but the root cause is usually database pressure.

Common hidden issues:

Autoloaded options growing silently
Meta queries that scale poorly
Missing or ineffective indexes
Repeated queries that caching hides until traffic spikes

Database health determines how far caching can take you.

Design Rationale and Trade-offs

Autoload is convenient but dangerous

Everything in autoload=yes loads on every request. This is easy to abuse unintentionally.

Meta flexibility costs performance

The wp_postmeta table trades structure for flexibility. At scale, this becomes expensive.

Indexes are workload-specific

Indexes should reflect how you query, not just schema defaults.

Practical Guardrails

Audit autoload size regularly
Avoid complex meta queries on high-traffic routes
Profile before adding indexes

Key Takeaways

Database problems surface late and hurt badly
Reduce work before adding hardware
Measure query count and execution time together

What you will learn

how to find the real slow queries, not the guesses
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 MySQL query cache is not a modern strategy

1. Start with evidence: find slow queries

Optimisation without measurement is just opinion.

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.

Enable slow query logging (carefully)

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 scan huge rows.

2. Understand the WordPress “hot tables”

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 are filtering products or posts by multiple meta fields, wp_postmeta is usually the pain point.

3. Add indexes, but add them with intent

Indexes can be transformative, and also dangerous if you apply them blindly.

The common “meta_key index” example

ALTER TABLE wp_postmeta ADD INDEX meta_key_idx(meta_key);

This can help certain patterns, but many WordPress queries filter by post_id and meta_key together. A composite index is often more useful (test on a copy first).

The workflow:

identify the slow query
inspect the query plan (EXPLAIN)
add the smallest index that supports that query shape
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)

Do not try to “index everything”. You will slow down writes and increase storage.

4. Clean up orphaned and expired data

Plugins come and go. Many leave their data behind. That does not just waste disk space, it can increase query overhead.

Common offenders:

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

Example clean-up query (use with caution and backups):

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

Prefer doing this on staging first, confirm nothing breaks, then repeat on production with a rollback plan.

5. Optimise autoloaded options (this is a big one)

Autoloaded options are loaded on every page load and can meaningfully affect time to first byte when the autoload size grows. cite

Measure autoload size

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

cite

Find the biggest culprits

SELECT option_name, LENGTH(option_value) AS size
FROM wp_options
WHERE autoload = 'yes'
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 fix is usually not “turn autoload off for everything”. The fix is “stop storing large, rarely used payloads in autoloaded options”.

6. MySQL query cache: do not rely on it

If you are on MySQL 8.0, the query cache is removed. cite

Even before removal, query cache did not scale well under concurrency. Modern WordPress performance relies on:

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

In other words: cache above the database, not inside it.

7. Storage engine and core database tuning

Ensure InnoDB

Most modern WordPress hosts run InnoDB, and you should too:

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 will cause disk I/O spikes.

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

innodb_buffer_pool_size=512M
innodb_log_file_size=256M

Do not tune in production by guesswork. Watch memory, I/O wait, and DB metrics.

8. Avoid common query mistakes in custom code

Two reliable wins:

Do not use `SELECT *` unless you need it

global $wpdb;

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

Avoid meta_query for everything

Meta queries are convenient, but can be expensive at scale. If you are building an app-like experience on WordPress (filters, dashboards, directories), you may outgrow wp_postmeta.

At that point, consider:

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

Real-world example

On a performance retainer for a content-heavy WordPress site:

reduced wp_options autoload from multiple megabytes down to a sensible size
removed orphaned metadata created by retired plugins
added a targeted index to support a repeated filter query
paired database fixes with Redis object caching to stabilise TTFB on peak traffic

FAQ

Should I “optimise the database” weekly?

Routine clean-up can help, but most wins come from fixing the underlying causes (autoload bloat, bad queries, missing indexes). If you do maintenance, automate it carefully and always take backups.

Is MariaDB better than MySQL for WordPress?

Both can be excellent. The bigger factor is your hosting environment, configuration, and query patterns.

When should I move data to custom tables?

When meta queries and joins are the bottleneck, and you have high-volume structured data that WordPress was not designed to query efficiently.

Need WordPress support? I provide maintenance and development for businesses across Cheshire. Learn more about my WordPress services or get in touch.