Database Indexing Explained: Which Index Type Actually Fits Your Query

When a query slows down, the instinct is to look at application code first — the ORM call, the ID lookup, the loop that fetches records one at a time. But just as often, the real bottleneck sits at the storage layer: the database is scanning millions of rows to answer a query that should only ever touch a handful of them. The fix is usually an index — but “add an index” is not a single move. Different index types solve genuinely different query shapes, and picking the wrong one means paying the storage and write cost of indexing without getting the read speedup you were after.

What an index actually does

Without an index, a database answering WHERE email = '[email protected]' has exactly one option: read every row, check the email column, keep going until it’s checked them all. This is a table scan, and its cost scales linearly with table size — fine at a thousand rows, unusable at a hundred million.

An index is a separate, precomputed data structure — physically sorted or hashed by the indexed column — that lets the database jump close to the matching rows directly. The database maintains this structure automatically on every write, trading write cost for read speed. That trade-off, not “indexes are free performance,” is the entire mental model worth keeping.

B-tree: the default for a reason

Most relational databases default to a B-tree (or one of its variants, like B+ trees) as the general-purpose index structure. A B-tree keeps keys sorted in a balanced tree, so both exact lookups and range queries traverse the tree in logarithmic time — O(log n) instead of O(n).

Because a B-tree handles both exact matches and ranges reasonably well, it’s the correct default whenever you’re unsure which query pattern will dominate. Reach for something more specialized only when you know the access pattern well enough to exploit it.

Hash indexes: faster, but narrower

A hash index computes a hash of the key and jumps directly to the matching bucket — no tree traversal at all. For pure exact-match lookups, this beats a B-tree.

The catch: hashing destroys ordering. A hash index cannot serve a range query (WHERE age > 30) or provide sorted output, because rows with adjacent values don’t land in adjacent buckets. Use a hash index only when you’re certain the access pattern is exclusively exact-match — a session-token lookup table is a good candidate; a timestamp column you’ll also want to range-query is not.

Composite indexes: column order is the whole design decision

A composite (multi-column) index covers several columns together, e.g. (last_name, first_name). It’s physically sorted by the first column, then the second within each group of matching first-column values — like a phone book sorted by last name, then first name within each last name.

This has a direct consequence: the index can serve a query filtering on last_name alone, or on last_name and first_name together, but cannot efficiently serve a query filtering on first_name alone — the index isn’t sorted that way, so the database would have to scan the whole structure anyway.

Covering indexes: skip the table entirely

Normally, an index gets the database close to the matching rows, but then it still has to fetch the full row from the table to read any column not in the index. A covering index eliminates that extra step by including every column the query needs directly in the index itself — the database answers the entire query from the index alone, never touching the table.

This removes a real cost (one extra disk/page lookup per matching row) but isn’t free to adopt everywhere: a covering index is larger than a narrow one, since it’s carrying extra columns, and it pays a larger write-update cost on every insert or update. It’s a targeted optimization for specific hot queries, not a default to apply to every index in a schema.

The cost side nobody skips past fast enough

Every index type shares the same tax: it must be updated on every write that touches its columns. A table with ten indexes pays that update cost ten separate times on every insert. This is why “just add an index” is not a universally safe move on a write-heavy table — the right question is always which specific query patterns are slow enough to justify the write-side cost of speeding them up, not “which columns could theoretically benefit.”

Takeaway

Indexing isn’t a single lever — it’s a small decision tree matched to query shape. B-tree for the general case, especially anything involving ranges or sorting. Hash indexes when the access pattern is provably exact-match-only. Composite indexes when queries filter on multiple columns together, ordered to match the most common filter pattern. Covering indexes when a specific hot query justifies skipping the table lookup entirely. Every one of them costs write throughput to gain read throughput — the job is matching the type, and the decision to add one at all, to queries that are actually slow, not sprinkling indexes on every column that might someday matter.