How to Read a MySQL EXPLAIN Plan (WordPress Edition)

Running EXPLAIN

Take any SQL query and put EXPLAIN in front of it. MySQL will return a row for each table involved, showing how it plans to access the data.

EXPLAIN SELECT p.ID, p.post_title
FROM wp_posts p
INNER JOIN wp_postmeta pm ON p.ID = pm.post_id
WHERE p.post_type = 'product'
  AND p.post_status = 'publish'
  AND pm.meta_key = '_price'
  AND pm.meta_value BETWEEN 10 AND 50;

The output is a table with columns like type, rows, key, and Extra. Each one tells you something specific about how MySQL is handling that part of the query.

The type Column (Most Important)

The type column tells you how MySQL accesses the table. This is the single most important thing to check. Ranked from best to worst:

Type	Speed	What It Means
const	Instant	Single row via primary key or unique index. The best you can get.
eq_ref	Very fast	One row per join via unique index. Ideal for JOINs.
ref	Fast	Multiple rows via non-unique index. Common and usually fine.
range	Good	Index scan for BETWEEN, >, < operations. Acceptable.
index	Slow	Full index scan. MySQL reads the entire index. Can answer the query without touching the table, but still scans everything.
ALL	Terrible	Full table scan. Every single row is read. If you see this on a table with more than a few thousand rows, that's a performance problem.

Rule of Thumb

If the type is ALL and the rows column shows more than 10,000—you have a problem that needs an index.

Other Columns That Matter

rows

An estimate of how many rows MySQL will examine to produce the result. Lower is better. If you expect 5 results but MySQL examines 156,000 rows to find them, the query is doing too much work.

key

Which index MySQL actually uses. If this shows NULL, no index is being used—the query is doing a full scan. Check the possible_keys column to see what indexes could have been used but weren't.

Extra

Watch for these flags in the Extra column:

Using filesort — MySQL can't use an index to sort, so it sorts in memory (or on disk for large results). Expensive for large result sets.
Using temporary — MySQL creates a temporary table to hold intermediate results. Common with GROUP BY and DISTINCT on unindexed columns.
Using index — The query can be answered entirely from the index without reading table data. This is good—also called a "covering index."
Using where — MySQL applies a WHERE filter after fetching rows. Normal, but if combined with type: ALL, it means filtering is happening after a full table scan.

Real WordPress Example: WooCommerce Product Query

Here's a query that filters WooCommerce products by price and stock status. On a store with 10,000 products:

EXPLAIN SELECT p.ID 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
WHERE p.post_type = 'product'
  AND p.post_status = 'publish'
  AND pm1.meta_key = '_price'
  AND pm1.meta_value BETWEEN 10 AND 50
  AND pm2.meta_key = '_stock_status'
  AND pm2.meta_value = 'instock';

-- Result:
-- pm1: type=ALL, rows=156000  <-- full table scan on postmeta
-- pm2: type=ref, rows=4       <-- OK, using index
-- p:   type=eq_ref, rows=1    <-- OK, primary key lookup

The first JOIN on pm1 does a full table scan of 156,000 rows because there's no index on (meta_key, meta_value). The fix:

CREATE INDEX idx_postmeta_key_post_value
  ON wp_postmeta (meta_key, post_id, meta_value(191));

After adding this composite index, the same query uses type: ref and examines under 200 rows instead of 156,000.

Common WordPress EXPLAIN Patterns

1. wp_options Full Table Scan

The wp_options table loads all autoload='yes' rows on every page. If your autoloaded data exceeds 1MB, this single query dominates TTFB. WordPress runs this before your theme even loads.

EXPLAIN SELECT option_name, option_value
FROM wp_options WHERE autoload = 'yes';
-- type: ALL (no index on autoload column by default)

2. Postmeta JOIN Explosion

Every meta_query in WP_Query adds another JOIN on wp_postmeta. Three meta conditions = three JOINs on the same unindexed table. The query planner multiplies the row estimates, and performance collapses.

3. Taxonomy Count Queries

On sites with 50,000+ posts, the admin term count queries (wp_update_term_count) do full table scans. These run on every post save and can take 2+ seconds on large installations.

When to Optimize (and When Not To)

Not every slow query needs fixing. Focus your effort where it matters:

Queries that run on every page load — These affect every visitor. Even shaving 50ms off a per-page query saves seconds at scale.
Queries over 100ms — Anything under 50ms is fine. Between 50–100ms, monitor it. Over 100ms, investigate.
Queries inside loops — A 10ms query called 200 times in a loop is a 2-second problem. Fix the loop, not the query.
Skip: one-time admin operations — A 3-second query that runs once when you save settings? Not worth optimizing. Focus on what your visitors hit.

How to Read a MySQLEXPLAIN Plan