Imagine a users table with 100 million rows. If someone logs in with an email address and there is no index on email, the database may have to check row after row until it finds the match.

That is slow. The database needs a shortcut to jump close to the right row. That shortcut is an index.

A database index is an extra data structure that helps the database find rows without scanning the whole table.

Indexes are one of the most useful database performance tools, but they are not free. They can make some reads much faster, but every index also needs storage and must be updated when rows are inserted, changed, or deleted.

An index that helps no real query is just extra work for the database.

Good indexing starts with the queries your application actually runs. In this chapter, we will look at how indexes work, the common index types, and how to choose indexes for real query patterns.

An index stores selected column values in a lookup-friendly structure, usually with a reference back to the table row.

For a normal B-tree index, the indexed values are kept in sorted order. That lets the database find a value without checking every row in the table.

For example:

Now a query like this has a fast path:

Without an index, the database may need to scan the table. With the index, it can search for the email in the index and then fetch the matching row.

The exact behavior depends on the database, table size, statistics about the table, and query plan. Small tables are often faster to scan. Large tables with filters that match only a small number of rows usually benefit from indexes.

Most indexes work in two steps:

An index helps most when it greatly reduces the number of rows the database must inspect.

This idea is called selectivity. In plain terms, it means "how much does this filter narrow the search?" A highly selective condition matches only a small part of the table.

Columns that match many rows can still be useful as part of a multi-column index or filtered index. But an index on only a boolean column is often not worth much because true or false may match a large part of the table.

The most common index structure in relational databases is a B-tree, or a close version of it such as a B+ tree.

B-tree indexes keep keys sorted. That makes them useful for:

B-trees are built to work well with disks and memory. Each node contains many keys, so the tree stays shallow even with millions of rows. A lookup usually touches only a small number of pages instead of scanning the whole table.

B-tree indexes are the default for many CREATE INDEX statements because they work well for common application queries.

A multi-column index contains more than one column.

This index is useful for:

The database can find one user's orders and read them in newest-first order.

Column order matters. The index (user_id, created_at) is not the same as (created_at, user_id).

For B-tree indexes, a useful rule of thumb is:

This is not a universal law, but it is a good starting point. Always confirm with a query plan, such as EXPLAIN.

A covering index contains all columns needed by a query.

For example:

A covering index might be:

If the database can answer the query from the index alone, it can avoid extra table lookups. PostgreSQL calls this an index-only scan when its internal visibility rules allow it. Other databases have similar ideas.

Covering indexes are wider, though. Wider indexes use more disk, more memory, and more write work.

Use them for important, frequently run queries where you have measured the benefit.

Indexes are also used to enforce rules and control how some databases store table data.

Primary and Unique Indexes

A primary key usually creates a unique index automatically.

The primary key index helps look up rows by id. The unique index on sku prevents duplicate SKUs and supports fast lookups by SKU.

Clustered Indexes

A clustered index affects the storage order of table data, depending on the database.

This is database-specific:

Clustered layout can help range scans because nearby rows are stored close together. But it also affects writes. Random primary keys can scatter writes more than increasing keys.

Different query patterns need different index structures.

Hash Indexes

A hash index passes each key through a hash function to find the bucket that holds the matching row reference. For equality lookups, this can be a direct jump to the right bucket.

Hash indexes do not help with range queries or ordered reads because hash values do not keep the original key order.

In many relational databases, B-tree indexes are still the safer default because they support equality, ranges, and ordering.

Bitmap Indexes

A bitmap index represents matching rows as a string of bits. Each bit says whether a row matches a value.

When a query has several filters, the database can combine those bitmaps quickly.

Bitmap indexes work well for analytics, especially for columns with only a few possible values, such as region, plan type, or status. The query below maps cleanly to an AND across three column bitmaps:

Bitmap indexes are usually a poor fit for busy application tables with many writes. Updates can be expensive, and locking can be tricky depending on the database.

Partial or Filtered Indexes

A partial index stores only rows that match a condition.

This is useful when most queries care about only part of a table, such as open orders, active users, or undeleted records. The index is smaller and cheaper than indexing every row.

Expression Indexes

An expression index stores the result of an expression.

This supports queries such as:

Without an expression index, applying a function to a column can prevent a normal index from being used.

Full-Text, Spatial, and Inverted Indexes

Some queries need specialized indexes:

Use these when the query pattern does not fit a normal B-tree.

Indexes improve some reads by adding write and storage work.

Every index adds cost:

An index on a column that changes often is more expensive than an index on stable data. A wide index over many columns is more expensive than a narrow one. An unused index is only extra cost.

This is why production systems should track unused and duplicate indexes.

Start from the query, not the table.

For each important query, ask:

For example:

A reasonable index could be:

This matches the equality filters first, then the sort column.

Confirm with EXPLAIN. If the database does not use the index, the index may not match the query, the filter may match too many rows, or statistics about the table may be stale.

Avoid these mistakes:

Indexes are extra data structures that help databases find, join, filter, and sort rows more efficiently.

B-tree indexes handle most common application queries. Multi-column indexes are often more useful than several single-column indexes when they match the query. Covering, partial, expression, full-text, spatial, bitmap, and hash indexes solve more specific problems.

The tradeoff is cost: indexes use storage, slow writes, and require maintenance. Good indexing starts with real queries, measured plans, and a clear understanding of which filters narrow the data.

The right index can turn an expensive query into a predictable lookup. The wrong set of indexes can slow the whole system down.