DEV Community

WP Multitool
WP Multitool

Posted on • Originally published at wpmultitool.com

How to Read a MySQL EXPLAIN Plan (WordPress Edition)

#wordpress #tutorial #database #webdev

You've Got a Slow Query. Now What?

You know your WordPress site has slow database queries. Maybe you spotted them in Query Monitor, or your hosting company sent you a warning. You've got a query that takes 3 seconds when it should take 30 milliseconds.

The next step is EXPLAIN. It's MySQL's built-in tool for showing you exactly how it executes a query — and where it goes wrong.

Most WordPress developers never use it. That's a shame, because it's the single most useful tool for database performance.

Running EXPLAIN

Take any SQL query and put EXPLAIN in front of it:

EXPLAIN SELECT p.ID, p.post_title
FROM wp_posts p
JOIN wp_postmeta pm ON p.ID = pm.post_id
WHERE pm.meta_key = '_price'
AND pm.meta_value > 100
AND p.post_type = 'product'
AND p.post_status = 'publish';
Enter fullscreen mode Exit fullscreen mode

MySQL returns a table with several columns. Here are the ones that matter:

The Columns That Matter

type — How MySQL Accesses the Table

This is the most important column. From best to worst:

Type Meaning Speed
const Single row lookup by primary key Instant
eq_ref One row per join via unique index Very fast
ref Multiple rows via non-unique index Fast
range Index range scan (BETWEEN, >, <) Good
index Full index scan (reads every entry) Slow
ALL Full table scan (reads every row) Terrible

If you see ALL on a table with more than a few thousand rows, that's your problem. MySQL is reading every single row to find matches.

rows — How Many Rows MySQL Expects to Examine

This is an estimate, not exact. But if your query returns 10 results and MySQL examines 500,000 rows to find them, something is wrong.

key — Which Index MySQL Uses

If this is NULL, MySQL isn't using any index. That usually means there isn't one that fits, and you need to create it.

Extra — Watch for These

  • Using filesort — MySQL sorts results without an index. Slow on large result sets.
  • Using temporary — MySQL creates a temporary table. Often seen with GROUP BY or DISTINCT.
  • Using where — MySQL filters rows after reading them. Normal, but combined with ALL, it means scanning everything.
  • Using index — MySQL can answer the query from the index alone without touching the table. This is ideal.

A Real WordPress Example

Here's a common slow query from a WooCommerce site:

EXPLAIN SELECT p.ID, p.post_title, pm.meta_value as price
FROM wp_posts p
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'
ORDER BY pm.meta_value + 0 ASC
LIMIT 20;
Enter fullscreen mode Exit fullscreen mode

Typical EXPLAIN output:

+----+-------+------+---------------+------+---------+------+--------+-----------------------------+
| id | table | type | possible_keys | key  | rows    | Extra                       |
+----+-------+------+---------------+------+---------+------+--------+-----------------------------+
|  1 | p     | ref  | type_status   | ...  |  12,500 | Using where; Using filesort |
|  1 | pm    | ref  | post_id       | ...  | 156,000 | Using where                 |
+----+-------+------+---------------+------+---------+------+--------+-----------------------------+
Enter fullscreen mode Exit fullscreen mode

What's wrong here:

  1. rows: 156,000 on wp_postmeta — it's scanning most of the table
  2. Using filesort — the ORDER BY can't use an index
  3. There's no composite index on (meta_key, post_id, meta_value)

The fix:

CREATE INDEX idx_postmeta_key_post_value
ON wp_postmeta (meta_key, post_id, meta_value);
Enter fullscreen mode Exit fullscreen mode

After this index, the same query examines ~12,500 rows instead of 156,000 and skips the filesort.

Common WordPress EXPLAIN Patterns

The wp_options Full Scan 

EXPLAIN SELECT * FROM wp_options WHERE autoload = 'yes';
Enter fullscreen mode Exit fullscreen mode

WordPress core adds an index on autoload since 6.4, but older installations don't have it. If you see type: ALL here, add:

CREATE INDEX idx_autoload ON wp_options (autoload);
Enter fullscreen mode Exit fullscreen mode

The Postmeta Join Problem

wp_postmeta is WordPress's most abused table. Plugins store everything there. The default indexes cover post_id and meta_key separately, but queries that filter on both often can't use them efficiently.

The Taxonomy Count Query 

EXPLAIN SELECT COUNT(*) FROM wp_posts p
JOIN wp_term_relationships tr ON p.ID = tr.object_id
WHERE tr.term_taxonomy_id = 42
AND p.post_type = 'post'
AND p.post_status = 'publish';
Enter fullscreen mode Exit fullscreen mode

This is usually fine with default indexes, but on sites with 50,000+ posts it can slow down. Watch for type: ALL on wp_posts.

When to Stop Optimizing

Not every query needs to be instant. Focus on:

  • Queries that run on every page load (autoload, active plugins, rewrite rules)
  • Queries that take more than 100ms
  • Queries that run inside loops (the N+1 problem)

A query that runs once during a weekly cron job and takes 2 seconds? Not worth optimizing.

Automating This

Reading EXPLAIN output manually works, but it's tedious when you have 200 queries per page load. WP Multitool automates this — its Slow Query Analyzer captures slow queries, runs EXPLAIN on each one, assigns a health score (green/yellow/red), and suggests the exact CREATE INDEX statement when an index would help.

No external APIs. Everything runs locally on your server. Disabled modules add zero overhead.

Originally published at WP Multitool Blog.

Find what's slowing your WordPress. WP Multitool — 14 modules, $50 lifetime, zero bloat. Built by Marcin Dudek.

Top comments (0)