MainExamplesHistoryRecommended Reading
A database index is like the index in a book: instead of reading every page to find a topic, you look it up in the index and jump straight to the right page. Indexes make database queries much faster.
How Indexes Work:
- Create a lookup structure: Like a sorted list of values and their locations
- Store pointers: Point to where the actual data lives
- Fast lookup: Find data without scanning entire tables
- Trade space for speed: Indexes use extra storage but make queries faster
Types of Indexes:
- B-tree: Most common, good for range queries
- Hash: Fast exact lookups, not good for ranges
- Composite: Index on multiple columns
- Unique: Ensures no duplicate values
When to Index:
- Frequently queried columns: Columns used in WHERE clauses
- Foreign keys: Speed up joins
- Sorting/grouping: Columns used in ORDER BY or GROUP BY
Trade-offs:
- Faster reads: Queries are much faster
- Slower writes: Inserts/updates must update indexes too
- Extra storage: Indexes take up space
FAQ
Should I index every column?
No! Too many indexes slow down writes. Index columns that are frequently queried or used in joins.
How much faster are indexed queries?
Dramatically faster! From O(n) table scan to O(log n) index lookup. For large tables, this can be the difference between seconds and milliseconds.