Indexing
Designing primary, secondary, composite, and clustered indexes to optimize SQL lookup paths.
What you'll learn
- B-Tree Index
- Hash Index
- Composite Index
- Covering Index
TL;DR
Designing primary, secondary, composite, and clustered indexes to optimize SQL lookup paths.
Concept Overview
Database indexes are data structures that improve the speed of data retrieval operations. Like a book's index, they allow the database to find data without scanning every row.
Indexes trade write speed and storage space for dramatically faster reads. Choosing what to index is a critical performance optimization.
Key Architectural Pillars
B-Tree Index
Most common index type. Balanced tree structure. Good for range queries and equality searches.
Hash Index
Fast for equality searches (WHERE id = 123) but cannot do range queries. Used for exact matches.
Composite Index
Index on multiple columns. Order matters! Index on (lastName, firstName) works for queries on lastName alone, but not firstName alone.
Covering Index
Index contains all columns needed for a query. Database never touches the actual table (super fast).