MySQL EXPLAIN ANALYZE for WordPress Query Optimization (MySQL 8.0.18+)

Home » MySQL » MySQL EXPLAIN ANALYZE for WordPress Query Optimization (MySQL 8.0.18+)
January 20, 2024 MySQL
MySQL EXPLAIN ANALYZE for WordPress Query Optimization (MySQL 8.0.18+)

EXPLAIN ANALYZE is a MySQL 8.0.18+ command that executes a query fully and returns its execution plan annotated with actual runtime statistics — actual rows processed, actual elapsed time in milliseconds per node, and loop counts for nested joins — alongside the optimizer’s estimated values. This is fundamentally different from plain EXPLAIN, which only shows the estimated plan without executing the query: EXPLAIN may show rows: 10 while EXPLAIN ANALYZE reveals actual rows: 47,382 — a discrepancy that exposes stale table statistics as the root cause of a slow query. The output uses FORMAT=TREE (the only format supported for ANALYZE): a hierarchical indented tree where each node shows the operation and after analysis the actual timing and row counts in the format (actual time=first_row..last_row rows=N loops=M). Reading the output: actual time=0.12..45.6 means the first row was returned 0.12ms after the node started and the last row at 45.6ms; rows=47382 is how many rows this node processed; loops=120 means this node executed 120 times (a nested loop join where the outer table returned 120 rows). Red flags: (1) actual rows much larger than estimated rows — run ANALYZE TABLE to refresh statistics; (2) a Filter node where many rows are read but very few pass the filter — the filter column needs an index; (3) a Sort node with large actual rows and high elapsed time — a filesort that needs a covering index. WordPress’s WP_Query with meta_query and orderby => ‘meta_value’ is one of the most common targets for this analysis — the generated SQL joins wp_posts to wp_postmeta twice (once for the filter, once for the sort key) and filesorts the result. The MySQL indexes and EXPLAIN post covered reading basic execution plans; EXPLAIN ANALYZE adds actual timing and row-count data that makes root-cause analysis unambiguous instead of estimated.

Problem: A WordPress real estate site with 80,000 listings uses WP_Query filtered by _listing_status = ‘active’ and ordered by _sort_priority. The query takes 4–6 seconds. Plain EXPLAIN shows “Using filesort” and estimates 200 rows — but actual page load proves tens of thousands of rows are being processed.

Solution: Run EXPLAIN ANALYZE FORMAT=TREE on the exact SQL generated by WP_Query (captured via the posts_request filter), identify actual row counts and per-node timing, then add targeted composite indexes and verify improvement with a second EXPLAIN ANALYZE.

// Capture the exact SQL WP_Query generates — log it for use in EXPLAIN ANALYZE
add_filter( 'posts_request', function( string $sql, WP_Query $q ): string {
    if ( $q->get( 'post_type' ) === 'listing' ) {
        error_log( 'Listing query: ' . $sql );
    }
    return $sql;
}, 10, 2 );

// The query being analyzed:
$query = new WP_Query( [
    'post_type'      => 'listing',
    'posts_per_page' => 20,
    'meta_query'     => [ [
        'key'   => '_listing_status',
        'value' => 'active',
    ] ],
    'orderby'  => 'meta_value',
    'meta_key' => '_sort_priority',
    'order'    => 'ASC',
] );

-- Step 1: Run EXPLAIN ANALYZE on the captured query
EXPLAIN ANALYZE FORMAT=TREE
SELECT SQL_CALC_FOUND_ROWS wp_posts.ID
FROM wp_posts
INNER JOIN wp_postmeta       ON ( wp_posts.ID = wp_postmeta.post_id )
INNER JOIN wp_postmeta AS mt1 ON ( wp_posts.ID = mt1.post_id )
WHERE wp_posts.post_type   = 'listing'
  AND wp_posts.post_status = 'publish'
  AND wp_postmeta.meta_key   = '_listing_status'
  AND wp_postmeta.meta_value = 'active'
  AND mt1.meta_key = '_sort_priority'
ORDER BY mt1.meta_value ASC
LIMIT 0, 20;

-- Sample EXPLAIN ANALYZE output (abbreviated):
-- -> Limit: 20 row(s)  (actual time=4812..4812 rows=20 loops=1)
--   -> Sort: mt1.meta_value ASC, limit to 20 per chunk
--      (actual time=4811..4811 rows=20 loops=1)
--     -> Filter: (wp_postmeta.meta_value = 'active')
--        (actual time=0.08..3198 rows=74312 loops=1)  <- 74k rows pass to Sort!
--       -> Nested loop inner join
--          (actual time=0.06..2880 rows=79841 loops=1)
--         -> Index lookup on wp_postmeta using post_id
--            (meta_key='_sort_priority')
--            (actual time=0.04..1200 rows=79841 loops=1)  <- estimated was 200!
--         -> Single-row lookup on wp_posts using PRIMARY
--            (actual time=0.018..0.019 rows=1 loops=79841)

-- Step 2: Refresh statistics (stale stats caused the 200-row underestimate)
ANALYZE TABLE wp_posts;
ANALYZE TABLE wp_postmeta;

-- Step 3: Add composite indexes to eliminate the filesort
-- Covering index for the status filter
ALTER TABLE wp_postmeta
    ADD INDEX idx_meta_status   (meta_key(20), meta_value(20), post_id);

-- Index for the sort key
ALTER TABLE wp_postmeta
    ADD INDEX idx_meta_priority (meta_key(20), meta_value(10));

-- Step 4: Re-run EXPLAIN ANALYZE — verify improvement
-- Expected: Sort node eliminated, actual rows drop from 74k to ~20
-- Typical result: 4800ms -> 8ms

NOTE: EXPLAIN ANALYZE actually executes the SELECT query — the result set is discarded but the full scan runs, acquiring locks and reading all rows. Do not run it on slow queries during peak traffic on production; use a read replica or run it during off-peak hours. For UPDATE and DELETE statements, EXPLAIN ANALYZE also executes the statement in MySQL 8.0 and modifies data — always test DML statements in a development database. Also, ANALYZE TABLE wp_postmeta briefly locks the table for statistics collection — on large tables this can block concurrent writes for a few seconds. Schedule ANALYZE TABLE as a monthly maintenance job during low-traffic periods to keep the optimizer’s row estimates accurate and prevent the stale-statistics problem that caused the 200-row underestimate in the example above.

Related posts