Core Code
Core Code Modern Dev Handbook
SYSTEM-DESIGN Contents
Constraints & Core Metrics
What Is System Design (Production-First) Functional vs Non-Functional Requirements Constraints, Assumptions, and Design Inputs Latency vs Throughput Tail Latency (p95/p99) and Why It Dominates UX SLI, SLO, SLA: The Reliability Contract Capacity Estimation Basics Bottlenecks and Queues (Little's Law Intuition)
Scaling & Traffic Management
Vertical vs Horizontal Scaling Load Balancing Fundamentals Caching Strategies Overview Cache Invalidation and Consistency Rate Limiting at Scale Backpressure: Preventing Overload Timeouts and Retries (Safe Defaults) Graceful Degradation and Traffic Shedding
Data & Consistency
Database Scaling Basics Replication: Read Replicas and Failover Consistency Models (Strong vs Eventual) Sharding Basics Partitioning and Hotspot Avoidance Idempotency and Deduplication Eventual Consistency and UX Schema Evolution and Backward Compatibility
Reliability Patterns
Timeouts: Bounding Damage Retry Strategies (Jitter, Caps, Budgets) Circuit Breaker Bulkhead Pattern Fallbacks and Graceful Degradation Load Shedding and Admission Control Backups and Restore Strategy Disaster Recovery (RPO/RTO)
Observability & Control
Golden Signals: Latency, Traffic, Errors, Saturation Metrics: SLIs and SLO Targets Logging at Scale (Structure and Cost) Correlation IDs and Context Propagation Distributed Tracing Overview Alerting and Runbooks Capacity Planning and Saturation Postmortems and Continuous Improvement
Case Studies
Case Study: URL Shortener Case Study: File Upload and Serving Case Study: Notification System Case Study: Chat System Overview Case Study: Rate Limiter Service Case Study: Social Feed (Fan-out Trade-offs) Case Study: Search Autocomplete Case Study: Analytics Ingestion Pipeline
Distributed Systems Foundations
CAP Theorem Consensus Basics Leader Election Distributed Locking Clock Skew Split Brain Quorum Reads and Writes Eventual vs Strong Consistency (Practical)
Service Communication Patterns
Synchronous vs Asynchronous Request-Response Pattern Message Queues Pub/Sub Pattern Event-Driven Architecture Saga Pattern CQRS Overview Outbox Pattern API Gateway Pattern Backend For Frontend (BFF)
Data at Scale Advanced
Index Design Deep Dive Query Planning Multi-Region Databases Read-After-Write Guarantees Global Databases Data Locality Large Table Strategies Background Jobs Design
Multi-Region Architecture
Multi-Region Basics Active-Active Active-Passive Geo DNS Cross-Region Replication Global Latency Optimization Regional Failover
Cost-Aware System Design
Cost vs Performance Storage Cost Trade-offs Cache Cost Analysis Overprovisioning vs Risk Cloud Pricing Mental Model Right-Sizing Instances
Security Architecture
Zero Trust Service-to-Service Auth Secrets Management Encryption at Rest Encryption in Transit DDoS Protection Multi-Tenant Data Isolation
System Design Interview
Design Twitter Design YouTube Design Uber Design Payment System Design E-commerce Platform Design Distributed Cache

Query Planning

Explore execution plans and performance optimization.
On this page

Query Planning Explains Why a Query Is Slow

In production, you do not optimize by guessing. You optimize by understanding how the database executes the query: which index it uses, how many rows it scans, and where it sorts. Query planning is the difference between “it feels slow” and “it scans 8M rows”.

What to Look For in a Plan

  • Which index is selected (or why none is)
  • Estimated rows scanned vs returned
  • Filesort or temporary table usage
  • Join order and join type

Common Root Causes

  • Missing composite index matching the WHERE + ORDER BY shape
  • Low selectivity predicates (status, is_active) used alone
  • Implicit conversions (string vs int) preventing index use
  • Large OFFSET pagination forcing scans

Stability Matters More Than Best-Case Speed

Query plans can change as data grows or distribution changes. A query that was fine at 100k rows can collapse at 10M rows. Production-first optimization targets stable plans under growth, not only fastest today.

Red flags in practice:
- Rows examined is orders of magnitude higher than rows returned
- Sorting happens after filtering (filesort) on a large set
- Join explodes row counts before filtering (bad join order)

Production-First Takeaway

Use query plans to explain latency. Optimize for low scanned rows, index-aligned sorting, and plan stability as data distribution evolves.

Multi-Region Databases →