<strong>Database indexing</strong> is creating a data structure that allows the database to find rows quickly without scanning the entire table. It's like a book index—instead of reading every page, you look up the page number.
The Library Catalog Analogy
Without an index, finding a book means checking every shelf (slow). With a card catalog (index), you instantly know which shelf to check (fast).
Books (Database Rows)
All your data
Card Catalog (Index)
Quick lookup
Search (Query)
Find what you need
Find User by Email
Email Index
O(log n) vs O(n)
Create index
CREATE INDEX idx_email ON users(email)
Database builds B-tree
Sorted structure for fast lookups
Query uses index
SELECT * FROM users WHERE email = 'john@example.com'
Fast lookup
Finds row in O(log n) instead of O(n)
Trade-off: Slower writes
Inserts/updates must update index too
Wrong
"Index every column for maximum speed"
Correct
<strong>Indexes have trade-offs</strong>. They speed up reads but slow down writes and use disk space. Only index columns you frequently query or sort by.
E-commerce product search:
Without index: Scan 1M products → 2 seconds
With index on 'name': Find product → 10ms (200x faster!)
Index on 'category', 'price' for filtering
Composite index on (category, price) for combined queries