Stage 2 · Lesson 3 of 10
Indexes and query plans
An is a sorted structure, usually a B-tree, that lets the database find rows without reading the whole table. It works like the index at the back of a book. With one, a lookup in 100 million rows takes a few page reads instead of millions.
Every index slows down writes and uses storage, so add them for queries you actually run. An index on several columns (a composite index) follows the : it only helps queries that use its first column. An index on (user_id, created_at) serves "posts by one user, newest first" but not "everyone's posts from yesterday".
To see what the database will do, read its with EXPLAIN (or EXPLAIN ANALYZE in ). It shows whether the planner uses an index or does a , reading every row.
Common trapblaming the database and reaching for sharding or a new technology when the slow query only needed the right composite index.
A question to check yourself, an AI practice session and 2 cheat sheet entries come with the full roadmap. Unlock
Resources for this stage
Start with· follow one of these
- CoursePaidGrokking Database Fundamentals for Tech Interviews
Design Gurus · Beginner · Subscription
Storage engines, indexes, transactions, isolation, replication, sharding and NoSQL models
- GitHub repoFreeSystem Design Interview Roadmap by karanpratapsingh
Karan Pratap Singh · Intermediate
Free: the databases, indexes, normalisation, ACID, sharding and replication chapters