WordPress Query Optimization — Indexes, EXPLAIN, and Smarter SQL

Why WordPress gets slow

WordPress's schema is designed for flexibility, not speed. The biggest culprit is the EAV-style wp_postmeta table that stores everything as post_id, meta_key, meta_value rows. Queries against this table grow expensive as data grows.

After 200,000 rows in wp_postmeta, even simple post lookups can take 5+ seconds without proper indexes. After 1 million, the site becomes unusable.

This article documents the four query patterns we tune in every WordPress performance audit, with EXPLAIN signatures and the fix for each.

Tool: enable the slow query log

Before any optimization, identify the actual slow queries:

SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;
SET GLOBAL log_queries_not_using_indexes = 'ON';
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';

Browse the site as a typical user for 30 minutes. Then:

mysqldumpslow -s t -t 20 /var/log/mysql/slow.log

Top 20 queries by total time. These are your optimization candidates.

Pattern 1 — wp_postmeta scan without index

The query (Yoast SEO meta lookup):

SELECT meta_value FROM wp_postmeta 
WHERE post_id = 12345 AND meta_key = '_yoast_wpseo_metadesc';

EXPLAIN shows:

type: ref
key: post_id
rows: 250
Extra: Using where

Rows = 250 means MySQL scans 250 postmeta rows just to find the one with _yoast_wpseo_metadesc. With 100 page loads per minute, that's 25,000 unnecessary row reads per minute.

Fix: composite index

ALTER TABLE wp_postmeta 
ADD INDEX idx_post_meta_key (post_id, meta_key);

After:

type: ref
key: idx_post_meta_key
rows: 1
Extra: Using index condition

Rows = 1. Query time drops 50-100ms per call.

WordPress core actually ships with this index but plugins sometimes drop or rename it during database upgrades. Verify:

SHOW INDEX FROM wp_postmeta;

Look for an index containing both post_id and meta_key in that order.

Pattern 2 — Inefficient meta_query

The query (a typical WooCommerce product filter):

SELECT p.* FROM wp_posts p
INNER JOIN wp_postmeta pm1 ON p.ID = pm1.post_id
INNER JOIN wp_postmeta pm2 ON p.ID = pm2.post_id
INNER JOIN wp_postmeta pm3 ON p.ID = pm3.post_id
WHERE p.post_type = 'product' AND p.post_status = 'publish'
  AND pm1.meta_key = '_price' AND pm1.meta_value BETWEEN 10 AND 100
  AND pm2.meta_key = '_stock_status' AND pm2.meta_value = 'instock'
  AND pm3.meta_key = '_visibility' AND pm3.meta_value = 'catalog';

Three joins on wp_postmeta plus a range filter. EXPLAIN often shows multi-million row scans.

Fix: dedicated lookup tables for filtered fields

WooCommerce ships with wp_wc_product_meta_lookup exactly for this — a flat table with product_id, sku, stock_status, stock_quantity, regular_price, sale_price, on_sale. Querying:

SELECT product_id FROM wp_wc_product_meta_lookup
WHERE stock_status = 'instock'
  AND min_price BETWEEN 10 AND 100;

Single table, indexed columns, returns in milliseconds.

For custom post types where you need similar filtering, create your own lookup table and populate via save_post hook.

Pattern 3 — Unbounded post_meta search

The query (full-text search alternative some plugins use):

SELECT post_id FROM wp_postmeta 
WHERE meta_value LIKE '%search term%';

EXPLAIN:

type: ALL
key: NULL
rows: 850000
Extra: Using where

Full table scan. With 850,000 rows, this takes 8+ seconds.

Fix: don't use LIKE on meta_value

This is fundamentally wrong. MySQL can't use indexes on LIKE '%X%' because the wildcard is at both ends.

Better approaches: 1. Use a real search index — Elasticsearch, Algolia, MeiliSearch, or WordPress's posts.post_content FULLTEXT index 2. Use `LIKE 'X%'` (wildcard only at end) — can use index if meta_value is indexed 3. Use `meta_key` lookups instead — store the searchable field as a meta_key, not meta_value

For WordPress full-text on posts:

ALTER TABLE wp_posts ADD FULLTEXT(post_title, post_content);

Then:

SELECT * FROM wp_posts
WHERE MATCH(post_title, post_content) AGAINST('search term');

Sub-100ms even on multi-million-row tables.

Pattern 4 — WordPress homepage with too many posts and ACF

Theme query:

SELECT * FROM wp_posts WHERE post_type = 'post' AND post_status = 'publish' 
ORDER BY post_date DESC LIMIT 20;

That returns 20 rows fast. The problem comes from what runs next: for each post, the theme calls get_field('hero_image'), get_field('subtitle'), etc. Each call queries wp_postmeta.

With 20 posts and 10 ACF fields each, you've added 200 separate wp_postmeta queries. Each takes 5-50ms. Total: 1-10 seconds added to homepage TTFB.

Fix: query meta in bulk

WordPress core has update_meta_cache():

$post_ids = wp_list_pluck($posts, 'ID');
update_meta_cache('post', $post_ids);

One query loads all meta for all posts. Subsequent get_field() calls hit the cache.

Better: use pre_get_posts filter to set update_post_meta_cache = true in your main query — which is the default but plugins sometimes disable it for "optimization."

Pattern 5 — Slow admin page

Admin slowness usually traces to:

SELECT COUNT(*) FROM wp_posts WHERE post_type = 'attachment' AND post_status = 'inherit';

This is the Media Library count. With 100,000+ attachments, this query alone takes 2-5 seconds.

Fix: bypass the count

add_filter('manage_upload_columns', function($cols) {
    // your customization
    return $cols;
});

add_filter('media_views_l10n', function($strings) {
    // strings that include counts
    return $strings;
});

// More aggressively:
add_filter('wp_count_attachments', function() {
    return (object) array('inherit' => 100000);
});

This returns a static count, eliminating the slow query at the cost of slightly stale numbers.

How to add indexes safely

Always test indexes on staging first. Adding an index on a large table can take 5-30 minutes during which the table is locked.

-- On staging
ALTER TABLE wp_postmeta 
ADD INDEX idx_post_meta_key (post_id, meta_key);

SHOW INDEX FROM wp_postmeta;

-- Verify EXPLAIN improves before production:
EXPLAIN SELECT meta_value FROM wp_postmeta WHERE post_id = 123 AND meta_key = '_yoast_wpseo_metadesc';

For production, use ALGORITHM=INPLACE, LOCK=NONE:

ALTER TABLE wp_postmeta 
ADD INDEX idx_post_meta_key (post_id, meta_key),
ALGORITHM=INPLACE, LOCK=NONE;

This adds the index online without blocking reads. Writes are briefly slowed but not blocked.

Common mistakes during query optimization

• Adding many indexes "just in case" — each index slows writes; only add indexes you measured
• Trusting EXPLAIN without considering data volume — what looks fast on staging (small data) crawls on production
• Forgetting ANALYZE TABLE — after adding indexes or bulk data changes, run ANALYZE TABLE wp_postmeta; to update statistics
• Optimizing the wrong query — measure first, optimize the queries the slow log actually flagged

Measuring success

Repeat the slow query analysis after changes:

mysqldumpslow -s t -t 20 /var/log/mysql/slow.log

If your top 5 slow queries no longer appear, you fixed them. Iterate on the next 5.

When to call a specialist

A database that's been slow for years usually needs a multi-day audit: identifying slow queries, designing indexes, refactoring custom code that builds inefficient queries. We do this routinely.

Database speed audit — typically 4-12 hours of work. For broader performance see WordPress speed recovery.