System Design AI

Query optimization in System Design Interviews: When to Use It and What to Say

When an interviewee talks about query optimization, the interviewer wants to hear that you understand how a system can serve the same logical request in many physical ways, and that you can pick the cheapest one. In one sentence, query optimization is the process of selecting the most efficient execution plan for a data request, usually by rearranging joins, adding indexes, or reshaping the data model.

The problem it solves: latency and cost

Imagine a social-media feed service that must pull the latest 20 posts for a user. The naive implementation scans the entire "posts" table, filters by the follower list, sorts by timestamp, and returns the top 20. If the table holds billions of rows, that scan costs seconds and thousands of dollars in compute. Query optimization reduces the work the database does, turning a full table scan into an index lookup or a pre-computed view, cutting latency from seconds to milliseconds and saving resources.

How it works: step-by-step

  1. Parse the SQL (or equivalent) statement – The engine builds an abstract syntax tree.
  2. Generate candidate plans – For each join, filter, and sort, the optimizer creates alternatives: use an index, reorder joins, push predicates down, etc.
  3. Estimate costs – The optimizer uses statistics (row counts, distinct values, index selectivity) to estimate I/O, CPU, and network cost for each candidate.
  4. Pick the cheapest plan – The plan with the lowest estimated cost wins.
  5. Execute – The chosen plan runs; the engine may still adapt at runtime if actual row counts differ dramatically.

Small worked example

Suppose we have two tables:

SQL
CREATE TABLE users(id BIGINT PRIMARY KEY, name TEXT);
CREATE TABLE orders(id BIGINT PRIMARY KEY, user_id BIGINT, created_at TIMESTAMP);

We need the most recent order for a given user:

SQL
SELECT * FROM orders WHERE user_id = 123 ORDER BY created_at DESC LIMIT 1;

Candidate 1 – Full scan: Scan all rows in orders, filter user_id = 123, sort, then limit. Cost is proportional to the total rows in orders.

Candidate 2 – Index on user_id, created_at: If we create a composite index (user_id, created_at DESC), the engine can jump directly to the newest row for that user. The cost drops to log(N) for the index lookup plus a single row fetch.

Candidate 3 – Materialized view: A view that stores the latest order per user (SELECT user_id, MAX(created_at) FROM orders GROUP BY user_id). Query becomes a simple key lookup, but we pay the cost of keeping the view up-to-date.

The optimizer will compare the estimated I/O of each candidate. With a realistic row count (say 100 million orders, 1 million users), the index is usually the right choice; the materialized view pays off only when this read is so hot that a single key lookup is worth the extra work on every write. Keep in mind that the optimizer only picks among plans the existing schema allows, so creating the index or the view is your design decision, not the optimizer’s.

Where it shows up in classic interview questions

Design QuestionSpot where query optimization matters
News Feed / TimelineSelecting the top-N recent items for a user; you discuss using a composite index or a materialized “feed” table to avoid scanning the whole activity log.
E-commerce product catalogFiltering and sorting by price, rating, and availability; you talk about covering indexes and query rewrite to push filters early.
Analytics dashboardAggregating millions of events for a time-range query; you bring up pre-aggregated tables or query-time roll-ups as alternatives.

In each case, the interviewer's follow-up will often ask you to justify the choice or compare it to caching, denormalization, or sharding.

Trade-offs and the alternative interviewer's angle

AspectQuery optimization (indexes, materialized views)Alternative (caching / denormalization)
Write amplificationIndexes add overhead on every write; materialized views need incremental updates.Caches are read-only; denormalized tables duplicate data, increasing write cost.
StalenessIndexes are always up-to-date; materialized views may be eventually consistent.Cache entries may be stale; denormalized copies need background sync.
ComplexityRequires careful statistics and cost modeling; can be opaque to developers.Caches are straightforward to reason about; denormalized schemas are explicit.
Resource usageIndexes consume extra storage; views consume compute for refresh.Cache consumes memory; denormalized tables consume storage.

Interviewers often probe: "What if the write rate spikes?" or "How would you handle hot keys?" Be ready to discuss how the optimizer's cost model changes with workload and how you might fall back to a cache or a denormalized table for hot paths.

A 30-second spoken version

"Query optimization is about picking the cheapest way to satisfy a data request. In a feed service you avoid scanning the whole activity log by using a composite index or a pre-computed feed table. The optimizer evaluates candidate plans using statistics, then selects the one with the lowest estimated I/O and CPU. The trade-off is extra storage and write cost, so you balance it against alternatives like caching or denormalization."

Common mistakes candidates make

  • Treating optimization as a single knob – Saying "I’ll just add an index" without explaining why that index matches the query pattern.
  • Ignoring statistics – Claiming a plan is optimal without mentioning row-count estimates or selectivity.
  • Over-engineering – Proposing a full-text search engine for a simple equality filter.
  • Forgetting write impact – Forgetting that every extra index slows down inserts and updates.
  • Skipping the alternative discussion – Interviewers expect you to compare to caching, sharding, or denormalization; not doing so looks shallow.

How to practice this

  1. Pick a classic design prompt (e.g., news feed) and write the main query you would need to serve the request.
  2. List at least two possible execution plans—one using an index or materialized view, another using a cache or denormalized table. Estimate the read-write cost for each.
  3. Study the worked design in the System Design AI library, then draw your own solution on the interview canvas, focusing on the query-optimization discussion.

Questions people ask

When should I bring up query optimization in an interview?

Mention it when the design involves filtering, sorting, or aggregating large datasets. Show that you understand the cost of a full scan versus an indexed or pre-aggregated approach.

How deep should I go into the optimizer’s cost model?

A high-level overview is enough: talk about row counts, selectivity, and I/O cost. You don’t need detailed formulas, just the factors that influence the choice.

What if I don’t know the database’s statistics?

State assumptions, e.g., "Assuming a uniform user-id distribution, the index reduces a scan from O(N) to O(log N)." Acknowledging uncertainty is acceptable.

Can I replace query optimization with a cache?

You can, but explain the trade-offs: caches are fast but may be stale, while indexes guarantee fresh results at the cost of extra storage and write overhead.