Scaling data / Intermediate / 5 min
Query optimization: reading an execution plan
One query, three indexes. Watch a full table scan become an index seek that skips the sort and stops after 20 rows.
Start the simulation → Change the load, break a part, watch what happens. Free, no sign-up.
In short
- What it is
- Query optimization means checking the execution plan (EXPLAIN) a database picks and adding the index that lets it read the fewest rows.
- Why it matters
- The same query can take 25 seconds or a fraction of a millisecond depending on whether an index matches it.
- The trade-off
- Each index speeds its queries but slows every write and takes space, so index for real query patterns, not every column.
- Say it in an interview
- "Run EXPLAIN ANALYZE, look for Seq Scans and Sorts on big tables, add a composite index with equality columns first and the ORDER BY column last."
Check yourself
EXPLAIN shows "Seq Scan" on a 100-million-row table for a query returning 20 rows. What does that mean?
Answer: The database reads every row because no index fits the WHERE clause. A sequential scan reads the whole table; an index matching the filter avoids it.
Why can a composite index (customer_id, status, created_at) skip the Sort step?
Answer: Rows come out of the index already filtered and in created_at order. With equality columns first and the ORDER BY column last, the index order is the result order, so LIMIT can stop early.
An index on customer_id alone is fast for most customers but slow for one huge customer. Why?
Answer: It still reads, filters and sorts all of that customer’s rows to return 20. Cost grows with rows matched by the index, so skewed data needs the more specific index.