Without an index, finding a row requires scanning the entire table. .
With a B-tree index, lookups are time. The index uses space. For billion rows, that's comparisons instead of billion.
Create indexes on:
- Columns in WHERE clauses
- Columns in JOIN conditions
- Columns in ORDER BY
Don't over-index:
- Each index slows down writes
- Each index uses storage
In interviews, when you define a database schema, mention which columns you'd index and why.