SQL Execution Plans

When you write a SQL query, you tell the database what data you want. But you do not tell it how to get that data. The database's query optimizer figures out the best strategy — should it use an index? Which index? Should it scan the whole table? How should it join the tables? The execution plan reveals all these decisions.

An execution plan is a step-by-step roadmap showing exactly how the database engine will execute your query. Reading execution plans is the single most important skill for SQL performance tuning. It turns you from someone who guesses at performance problems into someone who can diagnose them precisely.

How to View an Execution Plan

MySQL — EXPLAIN

Output:

MySQL — EXPLAIN ANALYZE (Actual execution)

This actually runs the query and shows real timing information.

PostgreSQL — EXPLAIN

PostgreSQL — EXPLAIN ANALYZE

SQL Server — Execution Plan

Key Operations in Execution Plans

Table Scan (Full Table Scan)

The database reads every single row in the table. This is the slowest operation for large tables:

This means: no useful index exists for your WHERE condition. The database has no choice but to check every row.

Index Seek

The database uses an index to jump directly to the matching rows. This is the fastest lookup:

This means: an index exists and the optimizer used it to find rows efficiently.

Index Scan

The database reads the entire index (but not the full table). Faster than a table scan but slower than an index seek:

This happens when the optimizer decides reading the index is cheaper than the table, but cannot seek to specific rows.

Nested Loop Join

For each row in the outer table, search for matches in the inner table. Good for small datasets:

Hash Join

Builds a hash table from one input, then probes it with the other. Good for large datasets:

Sort

Explicitly sorts data, usually for ORDER BY or to prepare for a merge join:

Reading a MySQL EXPLAIN Output

Interpretation:

• e table: Uses idx_salary with a range scan to find employees with salary > 50000. "Using filesort" means it needs an extra sort for ORDER BY.
• d table: Uses PRIMARY key with eq_ref (single row lookup per employee). Very efficient.

Common Performance Indicators

Real-World Example: Diagnosing a Slow Query

Run EXPLAIN:

If the plan shows a full table scan on Orders, add an index:

Run EXPLAIN again — the plan should now show an Index Range Scan instead of a Full Table Scan, and the query runs in milliseconds.

Real-World Example: Missing Index Detection

If the plan shows:

This means no index helps this query. Create one:

New plan:

Performance improvement: from scanning 500,000 rows to reading 150 rows directly.

Tips for Reading Execution Plans

1. Look for full table scans — these are your biggest performance killers
2. Check the rows column — high numbers mean the database examines too many rows
3. Look for "Using filesort" — this means an extra sorting pass is needed
4. Check join types — eq_ref and ref are good; ALL is bad
5. Compare estimated vs actual — use EXPLAIN ANALYZE for real numbers
6. Read from inside out — inner operations execute first

Common Mistakes to Avoid

Mistake 1: Never looking at execution plans. Many developers write queries and never check how they execute. Always EXPLAIN slow queries.

Mistake 2: Trusting row estimates blindly. Estimated row counts can be wrong if table statistics are outdated. Run ANALYZE TABLE to update statistics.

Mistake 3: Optimizing queries that are already fast. Focus on queries that actually cause problems. A query running once daily at 2 seconds is not worth optimizing over one running 10,000 times daily at 500ms.

Mistake 4: Adding indexes without checking the plan. Sometimes the optimizer ignores your index because it determined a full scan is actually faster for that specific query.

Best Practices

1. EXPLAIN every slow query before attempting to optimize it
2. Use EXPLAIN ANALYZE for actual timing information
3. Keep table statistics updated with ANALYZE TABLE
4. Look at the biggest cost first — optimize the most expensive operation
5. Test with production-like data — plans change based on data volume
6. Compare plans before and after adding indexes to verify improvement

Execution plans are your window into the database engine's decision-making process. Learning to read them transforms SQL performance tuning from guesswork into science. Whenever a query is slow, EXPLAIN is always your first step.