I used to run EXPLAIN on slow queries, stare at the output, and have no idea what I was looking at. It felt like reading hieroglyphics.

Then I realized: it’s just a map. Maps show you the route. EXPLAIN shows you the database’s route through your query.

What EXPLAIN Actually Shows#

When you prefix a query with EXPLAIN, MySQL doesn’t execute it. Instead, it shows you its execution plan: the strategy it will use.

EXPLAIN SELECT * FROM orders 
WHERE customer_id = 100 
ORDER BY created_at;

The output tells you which tables it’ll touch (and in what order), what access method it uses (index scan vs full table scan), how many rows it expects to examine, and which indexes it considers.

The Key Columns to Watch#

graph TD A[type Column] --> B{What value?} B -->|ALL| C[Full Table Scan
RED FLAG] B -->|index| D[Full Index Scan
Better but still scanning everything] B -->|range| E[Index Range Scan
Good for WHERE x BETWEEN...] B -->|ref| F[Index Lookup
Good for WHERE x = ...] B -->|const| G[Single Row Lookup
BEST - Primary/Unique Key] style A fill:#000000,stroke:#00ff00,stroke-width:2px,color:#fff style B fill:#000000,stroke:#00ff00,stroke-width:2px,color:#fff style C fill:#000000,stroke:#00ff00,stroke-width:2px,color:#fff style D fill:#000000,stroke:#00ff00,stroke-width:2px,color:#fff style E fill:#000000,stroke:#00ff00,stroke-width:2px,color:#fff style F fill:#000000,stroke:#00ff00,stroke-width:2px,color:#fff style G fill:#000000,stroke:#00ff00,stroke-width:3px,color:#fff

type: Access method (const > ref > range > index > ALL)

possible_keys: Indexes MySQL considers

key: Index MySQL actually uses (NULL = no index = problem)

rows: Estimated rows to examine (lower is better)

Extra: Additional info like “Using filesort” (expensive) or “Using index” (good, covered query)

Real Example from Oracle#

We had a report query taking 8 seconds. EXPLAIN showed:

  • type: ALL (full table scan on 2M rows)
  • key: NULL (no index used)
  • rows: 2,147,483

The query was:

SELECT * FROM transactions 
WHERE DATE(created_at) = '2024-01-15';

The problem? DATE(created_at) applies a function, preventing index use. Took me embarrassingly long to spot this. The fix:

SELECT * FROM transactions 
WHERE created_at >= '2024-01-15 00:00:00' 
  AND created_at < '2024-01-16 00:00:00';

After:

  • type: range
  • key: idx_created_at
  • rows: 1,842

Query time: 45ms.

The Pattern I Follow#

Run EXPLAIN on the slow query. Look for type: ALL (table scan) or key: NULL (no index). If the rows column shows millions for what should be a simple lookup, something’s wrong. Watch for “Using filesort” or “Using temporary” in the Extra column. Both are expensive. Fix the worst offender first, then re-run EXPLAIN.

EXPLAIN doesn’t just tell you what’s slow. It tells you why. The database shows its work, you just need to read the map.

Do you use EXPLAIN regularly? What’s the worst execution plan you’ve fixed?