What Is Database Indexing? Faster Queries, Explained

A database index is a separate, sorted data structure that lets the database engine locate rows quickly, without reading every row in a table. It works exactly like the index at the back of a book: instead of flipping through every page to find where “photosynthesis” is discussed, you look it up in the alphabetical index and jump straight to the right page. Without an index, the database has to do the equivalent of reading the whole book — a full table scan.

The problem indexes solve

Imagine a users table with ten million rows and this query:

SELECT * FROM users WHERE email = 'ada@example.com';

With no index on email, the database has no idea where that row lives. It reads all ten million rows, comparing each email until it finds a match (or reaches the end). That’s a full table scan, and it gets slower as the table grows.

Add an index on email, and the picture changes completely. The index is a sorted structure the engine can search efficiently, narrowing ten million candidates down to one in a handful of steps. Lookups that took a linear scan now take logarithmic time — a difference you feel the moment your data outgrows a few thousand rows.

How indexes work under the hood

Most relational indexes are built on a structure called a B-tree (technically a B+ tree). A B-tree keeps keys sorted and stays shallow and balanced, so even a table with billions of rows is only a few levels deep. To find a value, the engine walks from the root down to a leaf, following the branch whose range contains the key. Each step eliminates a large fraction of the remaining rows, which is why the search is logarithmic rather than linear.

B-trees are also good at range queries because the keys are stored in order. A query like WHERE created_at > '2026-01-01' can find the starting point and then read sequentially, rather than scanning the whole table. Databases use other index types too — hash indexes for exact-match-only lookups, and specialized structures for full-text search and geospatial data — but the sorted B-tree is the workhorse for most queries you’ll write in SQL.

The reason indexes matter so much is that they turn an expensive disk operation into a cheap one. This is the same principle behind caching: do the costly work once, store the result in a structure optimized for fast lookup, and reuse it. An index is essentially a pre-sorted, always-maintained lookup path into your data.

The cost: indexes aren’t free

If indexes make reads faster, why not index every column? Because they carry real costs.

• Writes get slower. Every INSERT, UPDATE, or DELETE has to update every index on the affected columns, keeping each sorted structure correct. A table with eight indexes pays that tax eight times on every write.
• They consume storage. An index is a full copy of the indexed columns plus pointers back to the rows. Index-heavy tables can use as much space for indexes as for the data itself.
• Unused indexes are pure overhead. An index the query planner never chooses still slows down writes and eats disk. Indexing is a trade, not a freebie.

The rule of thumb: index columns you frequently search, join, or sort on, and leave the rest alone. Primary keys are indexed automatically. Foreign keys almost always deserve an index because they’re used in joins.

Composite indexes and column order

You can index multiple columns together in a composite index, and the order of columns matters. An index on (last_name, first_name) can serve queries that filter by last_name alone, or by last_name and first_name together — but not by first_name alone, because the structure is sorted by last name first. This is often called the “leftmost prefix” rule. Getting composite-index column order right, matched to your most common queries, is one of the highest-leverage tuning moves available.

Reading the query plan

You don’t have to guess whether an index is being used. Every major relational database — PostgreSQL, MySQL, and others — exposes an EXPLAIN command that shows the query plan: how the engine intends to satisfy a query. It tells you whether a query does a sequential scan or an index scan, and roughly how expensive each step is. When a query is slow, EXPLAIN is the first place to look. Seeing “Seq Scan” on a large table where you expected an index lookup is the classic sign of a missing or unused index.

Indexes across the database landscape

Indexing isn’t unique to relational systems. The trade-offs shift depending on the model — a distinction our comparison of SQL vs NoSQL explores in more depth. NoSQL document stores index fields too, often requiring you to declare indexes explicitly for the queries you plan to run. And a newer category, the vector database, uses specialized approximate-nearest-neighbor indexes to search by similarity rather than exact match — the backbone of semantic search over embeddings. Even edge databases that run close to users rely on indexes to keep those low-latency lookups fast. The structure differs, but the core idea is constant: trade some write cost and storage for dramatically faster reads.

The takeaway

A database index is a sorted structure — usually a B-tree — that lets the engine find rows without scanning the whole table, turning slow linear lookups into fast logarithmic ones. Index the columns you search, join, and sort on; skip the rest, because every index slows writes and costs storage. Mind column order in composite indexes, and use EXPLAIN to confirm the planner is actually using what you built. Indexing well is one of the most reliable ways to make a database feel fast.