Use EXPLAIN ANALYZE and Composite Indexes to Optimise Slow WordPress MySQL Queries

Home » MySQL » Use EXPLAIN ANALYZE and Composite Indexes to Optimise Slow WordPress MySQL Queries
January 15, 2023 MySQL, Optimization
Use EXPLAIN ANALYZE and Composite Indexes to Optimise Slow WordPress MySQL Queries

MySQL’s EXPLAIN ANALYZE (introduced in MySQL 8.0.18) executes a query and returns the actual execution plan with real row counts and timing — unlike EXPLAIN alone, which shows estimated values that can differ by orders of magnitude from reality on tables with stale statistics. WordPress databases accumulate large tables over time: wp_options fills with transients and orphaned plugin data, wp_postmeta grows with every product attribute and ACF field, and wp_posts expands with post revisions. A query that runs in 2 ms on a 1,000-row table may take 4 seconds on a 500,000-row table when the wrong index is used — or no index at all. The Using filesort and Using temporary warnings in EXPLAIN output indicate that MySQL is performing disk-based sorting or creating an internal temporary table, both of which are expensive at scale. Composite indexes — covering multiple columns in the right order — eliminate both of these warnings for common WordPress query patterns like WHERE post_status = 'publish' AND post_type = 'post' ORDER BY post_date. A covering index includes all columns referenced in the SELECT, WHERE, and ORDER BY clauses, allowing MySQL to satisfy the query entirely from the index without reading the base table. ANALYZE TABLE updates table statistics so the query planner makes accurate cost estimates — running it after bulk inserts or deletions prevents the planner from choosing a suboptimal plan based on stale cardinality data. The MySQL slow query log is the most efficient way to discover which queries need optimisation on a live production site — set long_query_time = 1 and let it run for 24 hours to capture a representative sample. The MySQL slow query log post details the configuration steps for enabling the log and using mysqldumpslow to identify the worst offenders. The composite index post explains the theory behind column ordering in composite indexes that the examples below apply in practice. Always test index changes on a copy of the production database — adding an index locks the table briefly on MySQL 5.7 but uses Online DDL with minimal locking on MySQL 8.0.

Problem: WordPress databases on MySQL 5.7 accumulate stale table statistics after bulk operations, causing the query planner to choose full table scans over available indexes — and EXPLAIN alone reports estimated row counts that mask the real performance problem.

Solution: Use EXPLAIN ANALYZE (MySQL 8.0+) or EXPLAIN plus SHOW PROFILE (MySQL 5.7) to measure actual execution costs, run ANALYZE TABLE to refresh statistics, and add composite covering indexes for the most expensive WordPress query patterns.

-- Find slow queries on a live site (slow query log alternative)
SELECT
    digest_text,
    count_star,
    ROUND(avg_timer_wait / 1e9, 3) AS avg_ms,
    ROUND(sum_timer_wait  / 1e9, 3) AS total_ms
FROM performance_schema.events_statements_summary_by_digest
WHERE schema_name = 'helloadmin'
ORDER BY sum_timer_wait DESC
LIMIT 10;

-- Check the current execution plan (estimated values)
EXPLAIN
SELECT ID, post_title, post_date
FROM wp_posts
WHERE post_status = 'publish'
  AND post_type   = 'post'
ORDER BY post_date DESC
LIMIT 10;

-- Check with actual execution stats (MySQL 8.0.18+)
EXPLAIN ANALYZE
SELECT ID, post_title, post_date
FROM wp_posts
WHERE post_status = 'publish'
  AND post_type   = 'post'
ORDER BY post_date DESC
LIMIT 10;

-- Refresh table statistics after bulk inserts or deletes
ANALYZE TABLE wp_posts, wp_postmeta, wp_options;

-- Add a composite covering index to eliminate filesort on the above query
ALTER TABLE wp_posts
    ADD INDEX idx_type_status_date (post_type, post_status, post_date DESC)
    ALGORITHM=INPLACE, LOCK=NONE; -- online DDL, MySQL 8.0

-- Verify the new index is used
EXPLAIN FORMAT=TREE
SELECT ID, post_title, post_date
FROM wp_posts
WHERE post_status = 'publish'
  AND post_type   = 'post'
ORDER BY post_date DESC
LIMIT 10;
-- Expected: "-> Index range scan on wp_posts using idx_type_status_date" (no filesort)

NOTE: EXPLAIN ANALYZE actually executes the query and applies its side effects — never run it on INSERT, UPDATE, or DELETE statements on a production database, only on SELECTs. Use a read-only replica or a staging database copy for analyzing write query plans.

Related posts