Query Optimization

Query optimization is the art and science of making database queries execute faster by analyzing execution plans, adding appropriate indexes, restructuring queries, and tuning database configuration. A single unoptimized query can bring down a production system, while a well-tuned one can serve the same data in milliseconds instead of minutes. Every backend engineer must understand how the database query planner works, how indexes accelerate lookups, and how to diagnose common anti-patterns like the N+1 problem.

Why This Matters

Query optimization matters because database performance is often the primary bottleneck in application scalability. A query that runs in 50ms at 1,000 rows might take 50 seconds at 10 million rows if not properly optimized. Understanding execution plans, index selection, join algorithms, and common anti-patterns like N+1 queries is what separates engineers who build systems that scale from those who constantly fight production fires. In system design interviews, demonstrating that you can reason about query performance and data access patterns shows practical engineering maturity.

The Building Blocks

• Execution Plans: EXPLAIN and EXPLAIN ANALYZE reveal how the database executes a query — which indexes it uses, join algorithms, sort strategies, and estimated vs actual row counts.
• Index Selection: Choosing the right indexes (B-tree for range queries, hash for equality, composite for multi-column filters, covering to avoid table lookups) is the highest-impact optimization.
• N+1 Query Problem: When application code loops through results and fires a separate query per item, replacing this with a JOIN or batch IN query can reduce hundreds of queries to one.
• Query Rewriting: Restructuring queries — replacing correlated subqueries with JOINs, using CTEs for readability, or breaking complex queries into stages — can change the execution plan dramatically.
• Statistics & Cardinality: The query planner uses table statistics (row counts, value distribution) to estimate costs; stale statistics lead to bad plans, so regular ANALYZE/UPDATE STATISTICS is essential.

Under the Hood

The query optimizer sits between SQL parsing and execution. After parsing the SQL into an abstract syntax tree, the optimizer generates multiple candidate execution plans and estimates the cost of each using table statistics, index metadata, and cost models. It considers different join orders, join algorithms (nested loop, hash join, merge join), index scan vs sequential scan, and whether to materialize intermediate results.

The cost model assigns a numeric cost to each operation based on estimated I/O and CPU. For example, a sequential scan of a 10-million-row table might cost 100,000 units, while an index scan returning 100 rows costs 10 units. The optimizer picks the plan with the lowest estimated total cost. This is why stale statistics are dangerous — if the optimizer thinks a table has 100 rows when it actually has 10 million, it might choose a nested loop join that's catastrophically slow.

Index selection is the most impactful optimization lever. A B-tree index turns a full table scan into a logarithmic lookup. Composite indexes serve multi-column WHERE clauses. Covering indexes include all columns needed by the query, eliminating the need to fetch from the main table (a "heap lookup"). Partial indexes index only a subset of rows matching a predicate, reducing index size. The key insight is that indexes are a write-time investment for read-time payoff — each additional index slows writes and consumes storage.

How Companies Actually Do This

Shopify Runs MySQL across a massive multi-tenant platform where query optimization is critical — they use automated slow query detection, ProxySQL for routing, and strict query review processes to prevent any single merchant's query from affecting others.

Uber Optimizes queries across their Schemaless (MySQL-backed) storage layer, using query analysis to identify missing indexes and N+1 patterns that would otherwise cause cascading latency spikes during peak ride hours.

Figma Migrated from single PostgreSQL to horizontally sharded Postgres, requiring extensive query optimization to ensure cross-shard queries remained performant and shard-local queries used appropriate indexes.

Common Pitfalls

1. Blindly adding indexes for every slow query — each index adds write overhead, storage cost, and maintenance burden; analyze your workload to find the minimal set of high-impact indexes
2. Ignoring stale statistics — the query planner relies on table statistics to choose execution plans; if statistics are outdated after large data loads, you get terrible plans on perfectly indexed tables
3. Fixing N+1 at the ORM level without understanding the generated SQL — ORMs like Entity Framework or Hibernate can silently generate N+1 patterns; always inspect the actual SQL with logging enabled

Interview Questions Worth Practicing

1. Walk me through how you'd diagnose a query that suddenly became slow in production even though no code changed.
2. How would you optimize a query that joins 5 tables and returns results sorted by a computed column?
3. Explain the N+1 query problem and three different strategies to solve it.

The Tradeoffs

• Read Speed vs Write Speed: Every index speeds up reads but adds overhead to every INSERT, UPDATE, and DELETE — heavily indexed tables can have 10x slower writes
• Storage vs Performance: Covering indexes and denormalization consume more disk space but eliminate expensive table lookups and joins
• Simplicity vs Optimization: Highly optimized queries with hints, CTEs, and specialized indexes are faster but harder to maintain and understand

How to Explain This in an Interview

Here is how I would explain Query Optimization in a system design interview:

Start by explaining the query planner — it takes your SQL, generates multiple execution plans, estimates costs using table statistics, and picks the cheapest one. Then walk through the main optimization levers: indexes (B-tree for ranges, composite for multi-column filters, covering to avoid heap lookups), query rewriting (replacing N+1 patterns with JOINs, correlated subqueries with CTEs), and statistics maintenance (running ANALYZE so the planner has accurate data). Mention EXPLAIN ANALYZE as your primary diagnostic tool — it shows both the planned and actual execution, revealing where estimates diverge from reality. Finish by noting that optimization is a tradeoff: indexes speed reads but slow writes, and the right strategy depends on your workload's read/write ratio and access patterns.

Related Topics

• Database Indexes
• Database Scaling
• Materialized Views
• Database Architectures

Original DosiBridge notes for Query Optimization

This section is written specifically for this page, not copied from a general system-design checklist. The useful way to study Query Optimization is to connect the concept to a production service where growth exposes the first weak assumption in the original design. That framing changes the design discussion from definitions to operating choices.

What makes this topic distinct

• Query Shape: For Query Optimization, decide the query shape before choosing tools; otherwise the design looks clean but fails when query changes.
• Write Amplification: For Query Optimization, decide the write amplification before choosing tools; otherwise the design looks clean but fails when optimization changes.
• Replication Lag: For Query Optimization, decide the replication lag before choosing tools; otherwise the design looks clean but fails when query changes.
• Migration Safety: For Query Optimization, decide the migration safety before choosing tools; otherwise the design looks clean but fails when optimization changes.
• Data Ownership: For Query Optimization, decide the data ownership before choosing tools; otherwise the design looks clean but fails when query changes.

Production review question

If a team adopts Query Optimization, what observable signal proves it helped instead of only making the architecture diagram more fashionable?

Practical metric to track

Track query-optimization health as a named dashboard or review item: combine one user-facing metric, one saturation metric, and one correctness metric. For Query Optimization, this keeps the discussion grounded in behavior that can be measured after deployment instead of generic advice that sounds good in an interview but never gets verified.