What is an index in SQL? When should and shouldn't you use one?

Question

Accepted Answer

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.

## How an index helps

```sql
-- without an index on email, this scans EVERY row (slow on a big table) — O(n)
SELECT * FROM users WHERE email = 'ann@x.com';

-- create an index → the database can find the row directly — O(log n)
CREATE INDEX idx_users_email ON users(email);
-- now the same query uses the index → dramatically faster
```

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.

## When to use indexes

```text
✓ 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
```

## When NOT to use indexes (the trade-offs)

```text
✗ 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.

## Composite and other index types

```sql
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
```

## Why it matters

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.