The Core Idea
The Same Query, Many Possible Execution Strategies
When you submit a SQL query, the database's query planner/optimizer doesn't just execute it literally as written — it considers multiple possible execution strategies (which index to use, if any; which order to join tables in; whether to scan a full table or use an index) and picks the one it estimates will be cheapest, based on statistics it maintains about your data (table sizes, how many distinct values exist in a column, and so on).
Query optimization, as a skill, means understanding how to read what strategy the planner actually chose (via an EXPLAIN or EXPLAIN ANALYZE command), recognizing when that strategy is unnecessarily expensive, and either adding the right index or restructuring the query so the planner can choose a faster strategy instead.
💡 Memory Trick
Think of the query planner as a GPS navigation app. You tell it your destination (the query's logical result), and it considers several possible routes (execution plans), estimating the time each would take based on current traffic data (table statistics) before picking one. Reading an EXPLAIN plan is like looking at the turn-by-turn directions the GPS actually chose — and if you notice it's routing you through unnecessary detours (like a full table scan when an index exists), you can intervene, just as you might tell a GPS to avoid a particular road.
Reading an EXPLAIN Plan
What to Look For
1
Full Table Scan vs. Index Scan
An EXPLAIN plan will explicitly show whether a step is a 'sequential scan' / 'full table scan' (checking every row) or an 'index scan' (jumping directly via an index). Seeing a full table scan on a large table for a highly selective WHERE condition is a classic signal that a useful index is missing.
2
Join Order and Join Type
For queries joining multiple tables, the plan shows which table is processed first and what join algorithm is used (nested loop, hash join, merge join) — a poorly chosen join order or algorithm can dramatically increase the total rows processed, especially when a much smaller table should have been filtered first.
3
Estimated vs. Actual Row Counts
EXPLAIN ANALYZE (which actually runs the query, not just estimates) shows both the planner's ESTIMATED row count at each step and the ACTUAL row count that occurred — a large mismatch between the two often signals outdated table statistics, which can cause the planner to choose a genuinely poor execution strategy based on wrong assumptions about your data.
Common Fixes
Turning a Slow Query Into a Fast One
The most common fix is adding an index on a column used in a WHERE, JOIN, or ORDER BY clause that's currently forcing a full table scan — this alone resolves a large fraction of real-world slow queries. Beyond indexing, common query-level fixes include: avoiding SELECT * when only a few columns are actually needed (reducing the amount of data read and transferred), avoiding functions wrapped around indexed columns in WHERE clauses (like WHERE YEAR(date_column) = 2024, which prevents the index on date_column from being used at all, since the index is on the raw column value, not the function's result), and restructuring queries so the most selective filtering condition is applied as early as possible.
It's also important to periodically update table statistics (many database systems do this automatically, but not always promptly after large data changes), since the query planner's cost estimates — and therefore its execution plan choices — depend entirely on having a reasonably accurate picture of your data's actual size and distribution.
🖥️ Applied Scenario
A reporting query that used to run in under a second now takes 45 seconds, and you run EXPLAIN ANALYZE to investigate.
1
You find the plan shows a full table scan on your 8-million-row orders table, filtering on WHERE YEAR(order_date) = 2024, where order_date does have an index.
2
You recognize that wrapping order_date in the YEAR() function prevents the database from using the existing index — the index only knows raw date values, not the result of applying a function to them.
3
You rewrite the condition as WHERE order_date >= '2024-01-01' AND order_date < '2025-01-01', which expresses the same logical filter without wrapping the indexed column in a function, allowing the planner to use the existing index.
4
Conclusion: the query drops back down to sub-second execution, confirmed by re-running EXPLAIN ANALYZE and seeing an index scan (not a full table scan) in the new plan.
📌 Exam Application
Exam questions frequently present a slow query and its EXPLAIN plan, and ask you to identify the specific bottleneck (missing index, function wrapped around an indexed column, poor join order) and propose a fix. You may also be asked to explain the difference between EXPLAIN and EXPLAIN ANALYZE — the former estimates without running the query, the latter actually executes it and reports real timing and row counts alongside the estimates.
⚠️ Most Common Query Optimization Mistakes
The most common mistake is wrapping an indexed column in a function within a WHERE clause (like YEAR(date_column) or UPPER(name_column)) without realizing this silently prevents the index on that column from being used at all, forcing a full table scan even though a seemingly relevant index exists. Another frequent error is adding an index and assuming it will automatically be used — the query planner might still choose a full table scan if its statistics suggest that's cheaper for the specific query (for example, if the WHERE condition matches a very large fraction of the table's rows, a full scan can genuinely be faster than using an index).
✓ Quick Self-Test
Given a slow query's EXPLAIN plan showing a full table scan, can you identify at least two possible causes and propose a fix for each? Can you explain why wrapping an indexed column in a function (like YEAR() or UPPER()) can prevent that column's index from being used?
Next Lesson
Data Warehouses
→
← All Databases Lessons