What Indexes Are and How They Work
You'll learn: How database indexes speed up queries by creating organized shortcuts to your data.
The Core Idea
An index is a separate data structure that maintains an organized copy of specific columns from your table, along with pointers to where the full rows live. Think of it like a book's index at the back—instead of reading every page to find mentions of "normalization," you look up the word and jump directly to pages 47, 89, and 142.
Without an index, the database performs a full table scan—reading every row sequentially to find matches. With an index, it navigates a sorted, tree-like structure to locate data in logarithmic time instead of linear time.
How Indexes Work
Imagine a users table with millions of rows. When you query WHERE email = 'alice@example.com', without an index the database checks every single row. That's slow.
With an index on the email column:
- The database maintains a B-tree (or similar structure) of sorted email values
- Each entry points to the disk location of the corresponding row
- The query uses binary search through the sorted structure
- It jumps directly to the matching row(s)
The Trade-off
Indexes dramatically speed up reads (queries), but they slow down writes (inserts, updates, deletes) because the database must update both the table and all relevant indexes. They also consume additional disk space.
This is why you don't index everything—you strategically index columns frequently used in WHERE clauses, JOIN conditions, or ORDER BY statements.
Key Takeaway:
Indexes are data structures that create sorted, searchable pointers to table rows, trading extra storage and slower writes for dramatically faster query performance.