Stage 2 · Lesson 5 of 10
Indexes
Without an , finding rows means reading the whole table. That is fine at 1,000 rows and slow at 10 million. An index works like the index at the back of a book.
A B-tree index keeps a column in sorted order, so lookups, range filters and ORDER BY need only a few page reads. Add indexes for the queries you actually run: columns used in WHERE, JOIN and ORDER BY. indexes and unique constraints automatically, but not foreign keys, so index those yourself.
In a composite index (one on several columns), such as (customer_id, created_at), the order matters. It helps filters on customer_id alone, but not on created_at alone.
Common trapindexing everything: every index slows inserts and updates and takes space, so each one has to earn its place.
A question to check yourself, an AI practice session and 1 cheat sheet entry come with the full roadmap. Unlock
Resources for this stage
Start with· follow one of these
- CoursePaidGrokking Relational Database Design and Modeling
Design Gurus · Intermediate · $98 one-time
The modelling and normalisation decisions that stay with a codebase for years
- CourseFreeCS50's Introduction to Databases with SQL
Harvard University · Beginner
Free course from querying through schema design, indexes, transactions and scaling
Practise
Also worth your time
- Database Indexing Explained: B-Tree, Hash, Bitmap, R-Tree, and When to Use Each (opens in a new tab)Read
B-tree and composite indexes, plus the leftmost-prefix rule that trips people up
What sits under your queries: storage engines, transactions and isolation levels