Database Indexes: The Invisible Fix for Slow WordPress Queries

Every WordPress site eventually hits a point where pages that used to load instantly start taking a second or two longer, especially as content, meta, and plugin data pile up. Caching hides a lot of this, but caching cannot fix a query that scans hundreds of thousands of rows every time it runs. The fix is usually smaller and more surgical than people expect: a missing database index.

Indexes are the difference between MySQL or MariaDB scanning a table row by row and jumping straight to the rows it needs. On a small site this difference is invisible. On a site with a large wp_postmeta table, a busy WooCommerce store, or a custom plugin writing thousands of rows a day, it is often the single biggest lever you can pull for backend speed.

Why Indexes Matter More Than You Think

A database index works like the index at the back of a book. Without one, MySQL has to read every page to find a mention of your topic. With one, it jumps straight to the right pages. Every WHERE, JOIN, and ORDER BY clause in a SQL query is a candidate for an index, and WordPress core, plugins, and themes generate a huge number of these queries on every request.

The tricky part is that WordPress core ships with a reasonable default set of indexes, but plugins that add their own tables, or that query core tables in unusual ways, frequently do not. A plugin querying wp_postmeta by meta_value instead of meta_key, for example, is often doing a full table scan on every request, and no amount of page caching fixes that for logged-in users, cron jobs, or admin screens.

Spotting Missing Indexes with EXPLAIN

The standard way to diagnose a slow query is MySQL’s EXPLAIN command. It tells you how the database plans to execute a query without actually running it. You can pair this with wp-cli to pull real queries out of your site.

First, find a slow query. The Query Monitor plugin is the easiest way to see which queries are slow on a given page load. Once you have a candidate query, run it through EXPLAIN using wp-cli’s database passthrough:

wp db query "EXPLAIN SELECT post_id FROM wp_postmeta WHERE meta_key = 'campaign_id' AND meta_value = '482'"

Look at the output columns, particularly type, key, and rows:

• type: ALL means a full table scan. This is the red flag you are looking for.
• key: NULL means no index was used at all, even if one exists on the table.
• rows shows roughly how many rows MySQL expects to examine. A number close to your total row count on a large table is a sign the query is not using an index effectively.

You can check what indexes already exist on a table with:

wp db query "SHOW INDEX FROM wp_postmeta"

If a query’s WHERE clause references a column that has no matching index, that is your answer.

The wp_postmeta Problem

The wp_postmeta table is the single most common source of database slowness on WordPress sites, and it is worth understanding why. It is an EAV (entity-attribute-value) table: every row is a post_id, a meta_key, and a meta_value. This design is flexible, which is why plugins love it, but it comes with real costs.

WordPress core indexes post_id and meta_key, but meta_value is stored as a LONGTEXT column and is not indexed at all by default. That means any query filtering or sorting by meta_value, which is extremely common in custom fields, ACF setups, and WooCommerce order or product meta, forces a full scan of the relevant rows.

Making things worse, many plugins reuse generic or highly repeated meta_key values, which reduces how selective the existing index actually is. A table with a few million postmeta rows and a handful of frequently queried meta keys can turn an innocent looking custom field lookup into a query that takes hundreds of milliseconds instead of a few.

Safe Ways to Add an Index

Adding an index is not risk-free. It takes disk space, it adds a small write overhead (every insert or update now has to update the index too), and on very large tables the operation to build the index itself can lock the table or take a while to complete. That said, for most WordPress sites the tradeoff is well worth it, and it can be done safely with a few precautions.

1. Back up first. Always take a full database backup before schema changes, even ones that seem low risk.
2. Test on staging. Reproduce the slow query and confirm the index actually improves the EXPLAIN output before touching production.
3. Add the index with wp-cli or a direct query. For a targeted postmeta lookup, a composite index on meta_key and a prefix of meta_value is a common fix:

wp db query "ALTER TABLE wp_postmeta ADD INDEX meta_key_value (meta_key, meta_value(20))"

4. Use an online algorithm where possible. On InnoDB, MySQL and MariaDB support ALGORITHM=INPLACE for many index operations, which avoids locking the table for reads and writes during the build:

wp db query "ALTER TABLE wp_postmeta ADD INDEX meta_key_value (meta_key, meta_value(20)), ALGORITHM=INPLACE, LOCK=NONE"

Not every storage engine or MySQL version supports every combination of algorithm and lock mode, so if the statement fails, drop the extra clauses and expect a brief lock during the schema change. Run this during a low traffic window if the table is large.

After You Add an Index

Once the index is in place, re-run your original query through EXPLAIN and confirm the type and key columns have changed for the better, and that rows has dropped. If a plugin is generating the slow query, consider whether the plugin itself has an update or configuration option that avoids the problematic pattern altogether, since an index is a workaround for a query shape, not a fix for a poorly written one.

It is also worth noting that fast storage and a well configured server reduce the pain of an unindexed query without eliminating it. NVMe storage and a properly tuned database server, which is one of the things a managed host like ServerBorn handles at the infrastructure level, make full table scans less punishing, but they do not turn an O(n) scan into an indexed lookup. The index is still the real fix.

Takeaway

Slow WordPress sites are often blamed on plugins, themes, or hosting, but the database schema underneath is just as often the real bottleneck. Learn to read EXPLAIN output, keep an eye on wp_postmeta queries filtering by meta_value, and add targeted indexes carefully, with backups and staging tests in place. It is one of the few performance fixes that is both invisible to visitors and permanent once applied.