Preview — Pro guide
You are seeing a portion of this guide. Sign in and upgrade to unlock the full article, quizzes, and interview answers.
Sections
Related Guides
SQL Joins & Aggregations: INNER, LEFT, GROUP BY, and Aggregation Pitfalls
SQL & Databases
Subqueries and CTEs: WITH Clauses, Correlated Subqueries, and Recursive Patterns
SQL & Databases
SQL Query Optimization: Indexes, Query Plans, and Performance at Scale
SQL & Databases
Databases: Sharding, Indexing & Replication
High-Level Design
SQL Indexes and Query Performance: B-Tree, Composite, and Covering Indexes
Index selection is what separates a working SQL query from one that runs in production. This guide covers B-tree index mechanics, composite index column ordering, covering indexes for index-only scans, when indexes hurt writes, and how to read EXPLAIN plans — skills tested at Google, Stripe, and Meta for senior data and backend roles.
Why Index Knowledge Is a Senior-Level Signal
Most SQL courses teach how to write queries. Indexes are the topic that determines whether those queries are usable in production. A query that returns correct results in 3ms on a 10K-row dev database might take 45 seconds on the 50M-row production table.
The difference is almost always an index.
In senior data engineer and backend interviews, interviewers probe index knowledge directly: "How would you speed up this query?" and "Walk me through what EXPLAIN says here." At Google and Stripe, SQL performance questions appear in ~40% of senior data rounds.
What you need to know:
- How B-tree indexes work mechanically (binary search on sorted structure)
- The left-prefix rule for composite indexes — column order matters
- Covering indexes: when the index itself contains all the data the query needs
- When NOT to add indexes: write-heavy tables, low-cardinality columns
- How to read a basic EXPLAIN plan: seq scan vs index scan vs index-only scan