An index is a data structure (usually a B-tree) that lets the database find rows quickly without scanning the entire table — like a book's index. It dramatically speeds up lookups, filters, joins, and sorts on the indexed column(s), at the cost of extra storage and slower writes.
users email ;
INDEX idx_users_email users(email);
Without an index, the database does a full table scan (checking every row). With one, it navigates a B-tree to find matching rows in logarithmic time — the difference between scanning millions of rows and a handful of lookups.
✓ Columns in WHERE clauses (frequently filtered)
✓ Columns used in JOINs (foreign keys especially)
✓ Columns in ORDER BY (sorting can use the index)
✓ Columns with high SELECTIVITY (many distinct values — email, id)
✓ Primary keys (auto-indexed) and unique constraints
✗ Columns rarely queried — wasted storage and write overhead
✗ Small tables — a full scan is already fast; an index adds no benefit
✗ Low-selectivity columns (e.g. a boolean with 2 values) — index barely helps
✗ Write-heavy columns — EVERY INSERT/UPDATE/DELETE must also update the indexes
→ too many indexes SLOW DOWN writes significantly
Indexes aren't free: they consume storage and slow down writes (every modification must update the indexes). Over-indexing hurts write performance.
CREATE INDEX idx_users_country_name ON users(country, name); -- COMPOSITE (multi-column)
-- helps WHERE country = ? AND name = ?, and WHERE country = ? (leftmost prefix)
-- but NOT WHERE name = ? alone (order matters!)
CREATE UNIQUE INDEX ... -- enforces uniqueness
-- also: partial indexes, covering indexes, GIN/GiST (Postgres) for special data
Indexes are one of the most important topics in database performance — they're the primary tool for making queries fast, and understanding when and how to use them is essential knowledge for anyone working with databases at scale.
The core value is dramatic: without indexes, queries that filter, join, or sort on a column require full table scans (checking every row — catastrophically slow as data grows), while indexes turn these into fast logarithmic lookups — often the difference between a query taking milliseconds versus seconds or minutes.
Understanding when to index (frequently-filtered WHERE columns, JOIN/foreign-key columns, ORDER BY columns, high-selectivity columns) is fundamental for query performance.
Equally important is understanding the trade-offs and when NOT to index: indexes consume storage and, crucially, slow down writes (every INSERT/UPDATE/DELETE must update all indexes), so over-indexing hurts write performance, and indexes add little value on small tables or low-selectivity columns.
This balance — indexing what's queried while not over-indexing — is a key skill.
Knowing about composite indexes (multi-column, where column order matters for which queries they help) rounds out practical index knowledge.
Since query performance is critical for real applications, and since proper indexing (the right indexes on the right columns, balancing read speed against write cost and storage) is the most impactful database optimization, mastering indexes — how they work, when to use them, the write/storage trade-offs, and composite indexes — is core, high-value knowledge for performant databases and one of the most important and commonly-tested database topics.