Core indexing concepts, selectivity, left-prefix behavior, and practical heuristics for when to add an index.
Start with the fundamentals before choosing index types.
Index = extra data structure ordered by one or more keys so the engine can avoid scanning every row or document.Use indexes on columns or fields that appear frequently in `WHERE`, `JOIN`, `ORDER BY`, and uniqueness checks.
-- Better index candidate
WHERE email = ?
-- Often weaker candidate by itself
WHERE is_active = trueAn index on a highly selective column usually narrows results faster than an index on a low-cardinality flag.
More indexes => faster reads, slower writes, more storage, longer maintenance operations.Do not index everything. Add indexes to support real queries, not hypothetical ones.
INDEX (status, created_at, user_id)
Supports well:
WHERE status = ?
WHERE status = ? AND created_at >= ?
Usually not ideal alone for:
WHERE created_at >= ?For many B-tree implementations, the leading columns matter most for efficient lookups.
-- Common candidates
JOIN orders o ON o.user_id = u.id
ORDER BY created_at DESCForeign keys, join keys, and frequent sort keys are common indexing targets.
A fast decision framework before adding an index.
1. Is the query slow enough to matter?
2. Is the pattern common in production?
3. Is the filter selective?
4. Can one composite index cover multiple common queries?
5. Does EXPLAIN show scans or expensive sorts?
6. What write and storage cost will this add?Good indexing starts with workload evidence rather than intuition alone.
EXPLAIN SELECT * FROM orders WHERE status = 'paid' ORDER BY created_at DESC;Capture the baseline plan, add the index, then compare the new plan and latency.