Use EXPLAIN to find and fix slow MySQL queries

Home » Coding » Use EXPLAIN to find and fix slow MySQL queries
June 10, 2021 Coding, MySQL
Use EXPLAIN to find and fix slow MySQL queries

Slow database queries are one of the leading causes of sluggish WordPress sites, yet many developers never look beyond the surface symptoms of a slow page. MySQL’s built-in EXPLAIN statement is a free, zero-setup diagnostic tool that reveals exactly how the query optimiser plans to execute any SELECT statement — which indexes it will use, how many rows it expects to examine, and whether a dreaded full-table scan is about to bring your server to its knees. Reading EXPLAIN output takes about ten minutes to learn and can save hours of guesswork. The most important column to watch is type: values of ALL mean a full-table scan, while ref, eq_ref, or const indicate efficient index use. The rows column tells you how many rows MySQL expects to read — the lower the better. The Extra column warns about expensive operations like Using filesort or Using temporary. Once you understand the output, adding the right index often reduces query time from seconds to milliseconds. This is especially useful when you write custom $wpdb queries or when debugging the SQL generated by complex WP_Query arguments. You can also use EXPLAIN ANALYZE in MySQL 8.0+ to see actual versus estimated row counts. The examples below use the WordPress posts table so you can practice immediately on a real dataset.

Problem: A database query is slow and you need to find out why and how to fix it without guessing which column needs an index.

Solution: Run EXPLAIN in phpMyAdmin or the MySQL CLI, then interpret the output:

-- Basic EXPLAIN usage
EXPLAIN
SELECT ID, post_title, post_status
FROM ynYYAa7bFT_posts
WHERE post_status = 'publish'
  AND post_type   = 'post'
ORDER BY post_date DESC
LIMIT 10;

-- EXPLAIN output columns to watch:
-- type  : ALL (bad) | ref / eq_ref / const (good)
-- key   : the index MySQL chose (NULL = no index used)
-- rows  : estimated rows to scan (lower is better)
-- Extra : "Using filesort" or "Using temporary" = expensive

-- If key is NULL, add a composite index:
ALTER TABLE ynYYAa7bFT_posts
  ADD INDEX idx_type_status_date (post_type, post_status, post_date);

-- Re-run EXPLAIN to confirm the new index is used
EXPLAIN
SELECT ID, post_title, post_status
FROM ynYYAa7bFT_posts
WHERE post_status = 'publish'
  AND post_type   = 'post'
ORDER BY post_date DESC
LIMIT 10;

-- Check existing indexes on a table
SHOW INDEX FROM ynYYAa7bFT_posts;

NOTE: Adding indexes speeds up reads but slightly slows down INSERT, UPDATE, and DELETE operations because MySQL must maintain the index. Only index columns you actually filter or sort on. On a live WordPress site, use ALTER TABLE ... ADD INDEX during low-traffic hours, or use the pt-online-schema-change tool from Percona Toolkit to add indexes without locking the table. After making index changes, run OPTIMIZE TABLE to rebuild table statistics.

Related posts