Query Optimization Questions
Part of the Data Analyst Roadmap
In short
"This query takes ten minutes. How would you speed it up?" Interviewers ask this to see if you understand what the database actually does with your SQL.
'How would you make this slow query faster?' — testing your understanding of indexes, unnecessary joins, and query structure from Phase 3.
Think of it like this
Finding a book in the library
You can walk every shelf until you find the book. Or you check the catalogue drawer, find its shelf number and walk straight there. An index is that catalogue. A full table scan is walking every shelf.
Watch it happen
Full scan vs index lookup
Full table scan
- Reads every row to find matches
- Fine for small tables
- Slow on hundreds of millions
Index lookup
- Jumps straight to matching rows
- Needs an index on that column
- Blocked by functions on the column
For selective filters on big tables, an index wins. In interviews, explain both, and say you'd check the query plan with EXPLAIN.
The big ideas
What you need to know
Filter early
Reduce rows before joins and aggregations. Less data in each step means less work.
Select only needed columns
SELECT * reads more data, especially in columnar warehouses where you pay for columns scanned.
Indexes and partitions
Indexes speed lookups in databases like Postgres. Partitioning by date lets warehouses skip whole chunks.
Read the plan
EXPLAIN shows whether the database scans everything or uses an index. Mention it in every answer.
See it in code
Walk through the code
1-- Slow: wrapping the column in a function blocks the index2SELECT * FROM orders3WHERE DATE(created_at) = '2026-10-01';4 5-- Faster: a plain range the index can use, fewer columns6SELECT order_id, customer_id, amount7FROM orders8WHERE created_at >= '2026-10-01'9 AND created_at < '2026-10-02';10 11CREATE INDEX idx_orders_created_at ON orders (created_at);12EXPLAIN SELECT order_id FROM orders WHERE created_at >= '2026-10-01';1/4DATE() runs on every row, so a normal index on created_at can't be used.
A favourite interview example. Rewriting the filter as a range lets an index on created_at do its job.
Save yourself the pain
Common mistakes
Oops
Answering only "add an index" for every slow query.
Do this instead
Walk through filters, columns, joins, the plan, then indexes or partitions, and mention their trade-offs.
Oops
Forgetting that indexes slow down inserts and use space.
Do this instead
Say indexes help reads but cost writes, so you add them for frequent, selective filters.
Your mission
See an index work
Quick check
Which change is most likely to help a slow query that filters a huge table with WHERE LOWER(email) = 'a@b.com'?
Go deeper
- 1Review Phase 3's query optimization basics before technical interviews.
- 2Practice explaining why SELECT * is slower than selecting only needed columns.
- 3Learn to reason about a query's likely bottleneck even without running EXPLAIN live.
More sql & technical at this stop
Live SQL Coding Questions
Data Modeling Questions
Stuck on this topic? Ask an Insider
Get 1:1 guidance from people who've walked this exact path — on the InsideEdge app.
