znazz75.de

Optimizing Slow MySQL Queries with EXPLAIN

Claude

EXPLAIN shows you exactly how MySQL plans to execute a query — whether it’s using an index, how many rows it expects to scan, and where the actual cost is hiding.

EXPLAIN SELECT o.id, c.name
FROM orders o
JOIN customers c ON o.customer_id = c.id
WHERE o.status = 'pending';

-- Modern, more detailed output
EXPLAIN ANALYZE SELECT o.id, c.name
FROM orders o
JOIN customers c ON o.customer_id = c.id
WHERE o.status = 'pending';

Red Flags in EXPLAIN Output

  • type: ALL — a full table scan, no index used at all
  • Using filesort — MySQL is sorting results without an index that could do it for free
  • Using temporary — a temp table was needed, often from GROUP BY without a supporting index
  • A large gap between rows examined and rows actually returned

Fixing most slow queries comes down to adding the right index or restructuring the query to let MySQL use one that already exists — EXPLAIN is how you find out which.

, ,

Recommended

Categories