How Do SQL Indexes Make Queries Faster?

A SQL index is a separate data structure — usually a B-tree — that lets the database find rows matching your WHERE clause without scanning every row in the table. Well-chosen indexes can turn a seconds-long full table scan into a millisecond lookup. The trade-off is slower writes and extra storage, so the art of indexing is choosing the columns you actually query by and leaving the rest alone.

How a B-tree index finds rows so fast

Think of a book index: instead of reading all 500 pages to find mentions of "transaction isolation", you look up the term alphabetically and jump straight to the listed pages. A B-tree index works the same way. It stores the indexed column's values in sorted order across a shallow tree, so finding any value takes a handful of node visits — logarithmic in the table size — followed by a direct hop to the matching table rows via stored pointers.

The numbers make the value obvious. Scanning a million-row table without an index reads all million rows: O(n). With a B-tree index, the database descends roughly three levels and reads only the matching rows plus a few index pages: O(log n) plus the result set. That is why an unindexed lookup that takes two seconds can drop to two milliseconds once the right index exists. Primary keys get this structure automatically — declaring PRIMARY KEY creates a unique index behind the scenes — and foreign keys and frequently filtered columns are the usual next candidates.

Creating indexes: syntax and examples

The basic syntax is one line. Say you have a users table and you constantly look people up by email:

CREATE INDEX idx_users_email ON users (email);

-- Cover the common query completely: email lookup returning name
CREATE INDEX idx_users_email_name ON users (email, name);

-- Unique index doubles as a constraint: no duplicate emails
CREATE UNIQUE INDEX idx_users_email_unique ON users (email);

A composite index on (email, name) can answer SELECT name FROM users WHERE email = ? from the index alone, without touching the table — a covering index. Column order matters: the index serves queries that filter on email, or on email plus name, but not queries filtering on name alone, because the tree is sorted by the leftmost column first. Put the most selective, most frequently filtered column first.

Indexes also accelerate JOINs and ORDER BY. Joining orders.user_id to users.id is far cheaper when both sides are indexed, and sorting by an indexed column can skip the sort step entirely. If joins are the slow part of your queries, the SQL join types guide explains what the planner does with each join shape — indexes and join strategy are two halves of the same performance story.

Which columns should you index?

Index by evidence, not instinct. Enable your database's slow query log (or pg_stat_statements on Postgres), find the queries that actually hurt, and check their plans with EXPLAIN. Then apply these rules:

• Index columns in WHERE clauses and JOIN conditions first — they decide which rows are read.
• Index foreign keys. Most databases do not create these automatically, and unindexed foreign keys make joins and cascading deletes slow.
• Consider composite indexes for queries that filter on several columns together, leftmost-first.
• Use partial indexes for hot subsets, such as WHERE status = 'active' on a table full of archived rows.
• Skip low-cardinality columns used alone: an index on a boolean rarely beats a scan, since half the table matches either way.

A partial index example shows how targeted you can be — small, fast, and cheap to maintain:

CREATE INDEX idx_orders_active ON orders (created_at)
  WHERE status = 'active';

The same evidence-first habit applies to schema design generally; the SQL join types reference is a handy companion when you are deciding which relationships deserve both a join and an index.

Why is my index not being used?

This is the most-searched indexing question after the basics, and the causes are remarkably consistent. First, the table is small: if the whole table fits in a few pages, the planner correctly prefers a sequential scan, and your index will only kick in as data grows. Test with realistic volumes before concluding the index failed. Second, the query wraps the column in a function — WHERE YEAR(created_at) = 2026 or WHERE LOWER(email) = ? — which hides the raw value from the index. Rewrite to sargable form (WHERE created_at >= '2026-01-01') or add an expression index on the function result.

Third, a type mismatch silently disables the index: comparing a bigint column to a quoted string forces a cast on every row. Fourth, the planner's statistics are stale, so it misjudges selectivity — run ANALYZE after bulk loads. Fifth, leading wildcards (LIKE '%gmail.com') cannot use a B-tree, since the tree is sorted from the left; trigram indexes exist for exactly this case. When in doubt, prefix the query with EXPLAIN and read what the planner actually chose: "Seq Scan" where you expected "Index Scan" tells you where to look.

The costs of over-indexing

Every index is a second data structure the database must maintain. Each INSERT writes to the table plus every index; each UPDATE to an indexed column rewrites index entries; each DELETE removes them. A table with a dozen indexes can spend more time maintaining indexes than storing data, and heavy write workloads feel it first. Indexes also consume disk and precious buffer cache — space used by an index nobody queries is space stolen from data and indexes that matter.

The fix is periodic pruning. Postgres exposes pg_stat_user_indexes, where idx_scan = 0 flags indexes that have never served a query; MySQL's performance schema offers similar visibility. Drop the unused ones and re-measure. A healthy rule of thumb is a handful of well-evidenced indexes per table — covering keys, join columns, and proven slow filters — rather than an index on every column "just in case". For a machine-readable companion to the join side of query tuning, see the SQL join types dataset.