Diagnosing Unindexed Slow Queries in MySQL 8.0 with EXPLAIN ANALYZE

A single poorly indexed SQL query inside a custom plugin or heavy archive template can saturate your CPU cores and stall dozens of PHP workers. Here is how we surgically locate and resolve database bottlenecks using MySQL 8.0 telemetry.

Act 1: The Crime Scene

A client’s catalog page suffered a 2.4s TTFB whenever filters were applied. Checking MySQL processlist during live traffic:

SHOW FULL PROCESSLIST;
-- Result: 18 threads stuck in "Creating sort index" or "Sending data"

The query was selecting orders by status, date range, and customer ID, generating 400,000 row inspections per request.

Act 2: Deep Analysis with EXPLAIN ANALYZE

Running EXPLAIN ANALYZE directly on the offender revealed the execution cost:

EXPLAIN ANALYZE 
SELECT id, post_title, post_date 
FROM wp_posts 
WHERE post_type = 'product' AND post_status = 'publish' 
ORDER BY post_date DESC LIMIT 20 OFFSET 10000;

• Actual loop execution: -> Table scan on wp_posts (cost=84210.42 rows=382100) (actual time=142.11..1824.30ms)
• The database engine had to read 380,000 rows into memory and sort them on disk via temporary tables before returning 20 records.

Act 3: Adding High-Cardinality Composite Index

Adding a targeted composite index covering the filter conditions eliminated the full table scan:

ALTER TABLE wp_posts 
ADD INDEX idx_type_status_date (post_type, post_status, post_date);

Re-running the query with EXPLAIN ANALYZE:

• Index scan cost: Reduced from 84,210 down to 14.8.
• Query execution time: Reduced from 1,824ms down to 1.1ms.

🔗 Interconnected Engineering Triages & Architecture Mesh:

Infrastructure bottlenecks are rarely isolated. Upstream server performance, database query patterns, and DOM execution are tightly interlocked:

• Debugging 504 Gateway Timeouts in Nginx & PHP-FPM: Resolve upstream socket exhaustion and pool queue stalls.
• Eliminating Render-Blocking CSS & Optimizing Critical Path: Inline above-the-fold styles and defer secondary stylesheets for sub-800ms FCP.
• TCP BBR vs CUBIC: Slashing Mobile Latency and Packet Loss: Overcome cellular packet loss and maintain full transmission bandwidth.