text
Index = extra data structure ordered by one or more keys so the engine can avoid scanning every row or document.Notes
Use indexes on columns or fields that appear frequently in `WHERE`, `JOIN`, `ORDER BY`, and uniqueness checks.
Database Indexing Basics and Access Patterns
Core indexing concepts, selectivity, left-prefix behavior, and practical heuristics for when to add an index.
Export
Copy the compact sheet, download it, or print it.
Download
`D` dense toggle · `C` copy all
Core concepts
Start with the fundamentals before choosing index types.
Prefer high-selectivity columns
Columns with many distinct values tend to benefit more.
sqlANYselectivitycardinality
sql
-- Better index candidate
WHERE email = ?
-- Often weaker candidate by itself
WHERE is_active = trueNotes
An index on a highly selective column usually narrows results faster than an index on a low-cardinality flag.
Remember the write cost
Every extra index must be maintained during inserts, updates, and deletes.
textANYwritesmaintenance
text
More indexes => faster reads, slower writes, more storage, longer maintenance operations.Notes
Do not index everything. Add indexes to support real queries, not hypothetical ones.
Composite index left-prefix rule
Use column order that matches real filter patterns.
sqlANYcompositeleft-prefix
sql
INDEX (status, created_at, user_id)
Supports well:
WHERE status = ?
WHERE status = ? AND created_at >= ?
Usually not ideal alone for:
WHERE created_at >= ?Notes
For many B-tree implementations, the leading columns matter most for efficient lookups.
sql
-- Common candidates
JOIN orders o ON o.user_id = u.id
ORDER BY created_at DESCNotes
Foreign keys, join keys, and frequent sort keys are common indexing targets.
Quick checklist
A fast decision framework before adding an index.
text
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?Notes
Good indexing starts with workload evidence rather than intuition alone.
Measure with EXPLAIN before and after
Validate improvement instead of guessing.
sqlANYexplainmeasurement
sql
EXPLAIN SELECT * FROM orders WHERE status = 'paid' ORDER BY created_at DESC;Notes
Capture the baseline plan, add the index, then compare the new plan and latency.
More in Database Indexing
Indexing Anti-Patterns and Maintenance
Avoid over-indexing, duplicate indexes, low-value indexes, stale statistics, and maintenance mistakes.
EXPLAIN, ANALYZE, and Verify Index Usage
Use EXPLAIN tools across PostgreSQL, MySQL, SQLite, and MongoDB to prove whether an index actually helps.
Partial, Expression, and Functional Indexes
High-leverage index patterns for subsets of data and computed predicates across PostgreSQL, MySQL, and SQLite.
MongoDB Index Types and Query Patterns
Single-field, compound, multikey, text, wildcard, partial, TTL, and unique index patterns for MongoDB.
SQLite Indexes and Query Planner
SQLite CREATE INDEX patterns, expression and partial indexes, EXPLAIN QUERY PLAN, and lightweight performance tuning.
MySQL Index Strategies and Verification
Composite indexes, invisible indexes, descending indexes, generated-column indexing, and EXPLAIN workflows in MySQL.