Why Indexes Matter
You write a simple query: SELECT * FROM users WHERE email = 'john@example.com'. On your dev database with 100 rows, it's instant. In production with 10 million rows? 45 seconds.
The difference? An index.
The Phone Book Analogy
Imagine finding someone in a phone book:
WITHOUT INDEX
Start at page 1, check every name until you find "Smith". With 1000 pages, you might check all 1000.
WITH INDEX
Open to middle (M), too early. Open between M-Z (S-ish), close! Find "Smith" in ~10 steps.
Real Numbers: The Impact
Let's see the actual difference on a table with 10 million rows:
| Query Type | Without Index | With Index | Improvement |
|---|---|---|---|
| Find by email | 4,500ms | 0.5ms | 9,000x faster |
| Find by ID range | 3,200ms | 12ms | 267x faster |
| Count by status | 2,800ms | 45ms | 62x faster |
How B-Tree Indexes Work
The B-tree (Balanced tree) is the most common index type. Here's how it works:
[50] <- Root
/ \
[25, 35] [75, 90] <- Branch nodes
/ | \ / | \
[10] [30] [40] [60] [80] [95] <- Leaf nodes (point to rows)- Balanced: All leaf nodes are at the same depth
- Sorted: Values are ordered, enabling range queries
- Shallow: Even billions of rows need only 3-4 levels
Finding a Value
To find id = 40:
- Start at root: 40 < 50, go left
- At [25, 35]: 40 > 35, go right
- At leaf [40]: Found! Follow pointer to row
Creating Indexes
-- Basic index on a single column
CREATE INDEX idx_users_email ON users(email);
-- Unique index (also enforces uniqueness)
CREATE UNIQUE INDEX idx_users_email ON users(email);
-- Check existing indexes
\d users -- PostgreSQL
SHOW INDEX FROM users; -- MySQLThe Trade-off: Write Performance
Indexes aren't free. Every time you INSERT, UPDATE, or DELETE:
- INSERT: Must add entry to every index on the table
- UPDATE: Must update indexes on modified columns
- DELETE: Must remove entries from all indexes
- Storage: Each index uses disk space (often 10-30% of table size)
When NOT to Index
Under ~1000 rows, full scan is often faster than index lookup
Boolean columns or status with few values (active/inactive)
Logs, events, metrics - bulk inserts suffer
Don't index columns you never filter on
Real-World: GitHub's Index Strategy
GitHub's repositories table has billions of rows. They carefully index only columns used in WHERE clauses and JOINs. Their query patterns dictate indexes, not guesswork.
Key insight: They monitor slow queries in production and add indexes based on actual usage.
Selectivity: The Key Metric
Selectivity = unique values / total rows| Column | Unique Values | Total Rows | Selectivity | Index? |
|---|---|---|---|---|
| 10,000,000 | 10,000,000 | 100% | Yes! | |
| country | 195 | 10,000,000 | 0.002% | Maybe |
| is_active | 2 | 10,000,000 | 0.00002% | Rarely |
Key Takeaways
- Indexes trade write speed for read speed - Choose wisely based on your workload
- B-trees give O(log n) lookups - Even billions of rows need only a few comparisons
- High selectivity columns benefit most - Email: yes, Boolean status: usually no
- Monitor real queries - Let your actual workload guide indexing decisions
Next up: Index Types - Understanding when to use B-tree, Hash, GIN, and GiST indexes.