EXPLAIN and query planning — reading execution plans

Advanced5 min read·eng-06-004

sqlinterviewperformance

Concept

EXPLAIN shows the database's query execution plan — how it WILL execute a query before you actually run it. It's the primary tool for diagnosing slow queries.

MySQL EXPLAIN key columns:

type: The join type. const (best) → ref → range → index → ALL (worst — full table scan).
key: Which index was used. NULL means no index.
key_len: How many bytes of the index were used. Helps identify if a composite index is partially used.
rows: Estimated number of rows examined. Lower is better.
filtered: Percentage of rows passed by the WHERE condition.
Extra: Important flags: Using index (covering index, good), Using filesort (sort not using an index, potentially slow), Using temporary (needs a temp table, slow for large data).

EXPLAIN ANALYZE (PostgreSQL / MySQL 8.0+): Actually runs the query and shows real execution time and row counts (vs. estimated). More accurate. Use in a read replica or dev environment.

EXPLAIN FORMAT=JSON (MySQL): More detailed output including cost estimates.

Reading the plan: Start from the innermost operation (the first table accessed). Work outward. Look for:

type = ALL on large tables — missing index.
Using filesort — consider adding an index that covers the ORDER BY columns.
rows estimate much larger than actual — stale statistics. Run ANALYZE TABLE.

PostgreSQL: Uses different terminology. Seq Scan = full scan. Index Scan or Index Only Scan = index used. Hash Join / Nested Loop / Merge Join = join strategies.

Code Example

sql

-- MySQL EXPLAIN
EXPLAIN SELECT u.name, COUNT(o.id) AS order_count
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
WHERE u.active = 1
GROUP BY u.id
ORDER BY order_count DESC
LIMIT 10;

-- Sample EXPLAIN output (MySQL)
-- id | select_type | table | type  | key                 | rows  | Extra
-- 1  | SIMPLE      | u     | ref   | idx_users_active    | 1250  | Using index condition; Using temporary; Using filesort
-- 1  | SIMPLE      | o     | ref   | idx_orders_user_id  | 5     | NULL
--
-- Analysis:
-- u: type=ref, key=idx_users_active — good, uses index
-- Extra: "Using temporary; Using filesort" — the GROUP BY + ORDER BY can't be satisfied by an index
-- Fix: ensure the GROUP BY and ORDER BY columns are in an index, or accept the filesort for small datasets

-- EXPLAIN ANALYZE (PostgreSQL — actually runs the query)
EXPLAIN ANALYZE
SELECT * FROM orders WHERE user_id = 42 ORDER BY created_at DESC LIMIT 10;

-- PostgreSQL output:
-- Limit  (cost=0.42..2.85 rows=10 width=...)  (actual time=0.043..0.087 rows=10 loops=1)
--   ->  Index Scan using idx_orders_user_created on orders
--         Index Cond: (user_id = 42)
--         Filter: (deleted_at IS NULL)
-- Planning Time: 0.1 ms
-- Execution Time: 0.1 ms  ← actual time

-- Detecting missing index — type=ALL on a large table
EXPLAIN SELECT * FROM orders WHERE status = 'pending';
-- type=ALL, key=NULL → add index on status column

-- After adding index
CREATE INDEX idx_orders_status ON orders (status);
EXPLAIN SELECT * FROM orders WHERE status = 'pending';
-- type=ref, key=idx_orders_status → index used

php

<?php
// Get the raw SQL from Eloquent to paste into EXPLAIN
$query = Order::where('user_id', 42)->orderBy('created_at', 'desc');
$sql   = $query->toSql();
$bindings = $query->getBindings();
// Replace ? with actual values, then run EXPLAIN <that SQL>

// Or use DB::select to run EXPLAIN directly
$plan = DB::select('EXPLAIN ' . $query->toSql(), $query->getBindings());
dd($plan); // array of stdClass with type, key, rows, Extra, etc.

PreviousIndexes — B-tree, hash, full-text — when each is usedSQL Deep Dive NextNormalization — 1NF, 2NF, 3NF, BCNF with real examplesSQL Deep Dive