If your database queries are slow, an index is usually the first tool you reach for — and often the one that’s misused the most. Here’s a no-nonsense walkthrough of how SQL indexes work and how to use them well.
What an Index Actually Is
A database index is a sorted data structure — typically a B-tree — that the engine can traverse in logarithmic time instead of scanning every row. A full table scan is O(n); a B-tree lookup is O(log n). On a table with a million rows, that’s the difference between checking every single row and checking about twenty.
The Golden Rule
Index the columns you filter on (WHERE), join on, and sort on (ORDER BY). A composite index on (user_id, created_at) serves WHERE user_id = ? AND created_at > ? far better than two separate single-column indexes.
Common Mistakes
- Wrapping a column in a function (
WHERE LOWER(email) = ?) defeats the index. - A leading wildcard (
LIKE '%foo') makes a B-tree useless. - Too many indexes slow down writes, since every index must be updated on INSERT/UPDATE.
Measure, Don’t Guess
Always check the execution plan (EXPLAIN in MySQL/PostgreSQL) before and after adding an index. The planner’s cost estimate is the ground truth — your intuition about what “should” be fast often isn’t.