SQL Indexes: How to Speed Up Database Queries

A query that slows down as its table grows may be scanning far more rows than it returns. SQL indexes give the database a faster route to matching rows, often letting it jump to a small set instead of inspecting the whole table.

Indexes aren't free, and adding one to every column usually makes a database worse. This walkthrough matches indexes to actual query patterns while covering single-column indexes, B-trees, EXPLAIN, composite index order, and the cost of faster reads.

This article covers the "Performance" chapter of Learn SQL, where you can run these queries yourself.

What Is a SQL Index?

A SQL index is an in-memory structure that ensures that queries we run on a database are performant, that is to say, they run quickly.

PRIMARY KEY columns are indexed by default, ensuring you can look up a row by its id very quickly. That keeps a lookup like this fast even when users contains millions of rows:

SELECT *
FROM users
WHERE id = 8472;

However, if you have other columns that you want to be able to do quick lookups on, you'll need to index them.

CREATE INDEX index_name ON table_name (column_name);

It's fairly common to name an index after the column it's created on with a suffix of _idx. For example, a front-end frequently finds itself in a state where it knows a user's email but not their id, so an index on the email field keeps those lookups fast:

CREATE INDEX email_idx ON users (email);

The database can now use email_idx for queries such as:

SELECT id, username
FROM users
WHERE email = '[email protected]';

Create indexes in a database migration so the schema change is reproducible. The PostgreSQL CREATE INDEX documentation covers unique, partial, and expression indexes.

B-Tree Indexes Under the Hood

Most database indexes are just binary trees or B-trees! The tree can be stored in RAM as well as on disk, and it makes it easy to look up the location of an entire row.

By indexing a column, we create a new in-memory structure, usually a B-tree, where the values in the indexed column are sorted into the tree to keep lookups fast. In terms of Big-O complexity, a B-tree index ensures that lookups are O(log(n)) instead of the O(n) comparisons needed to scan every row.

A B-tree keeps keys sorted in balanced, fixed-size pages. Internal pages guide the search to leaf pages, whose entries identify matching table rows. Disk-based databases persist index pages on disk and cache frequently used index and table pages in memory. A query needs disk I/O when the pages it needs aren't cached.

B-tree indexes work well for:

• Equality predicates such as WHERE email = '[email protected]'
• Range predicates such as WHERE created_at >= '2026-07-01'
• Sorting such as ORDER BY created_at, when the index order matches the query

An index doesn't guarantee a faster query. If a predicate matches most of the table, a sequential scan may cost less than bouncing between an index and many table pages. The optimizer estimates those costs and chooses a plan.

Choosing Columns for SQL Indexes

The rule of thumb is simple:

Add indexes to columns you know you'll be doing frequent lookups on. Leave everything else un-indexed. You can always add another index later.

In other words, start with queries, not columns. Index columns used repeatedly in selective WHERE clauses, joins, and ordering. Correlated subqueries can also benefit when an index supports their repeated lookup.

Suppose an application runs this query on every account page:

SELECT id, amount, created_at
FROM transactions
WHERE user_id = 42
ORDER BY created_at DESC
LIMIT 50;

An index on (user_id, created_at) can find one user's transactions in the requested order. An index on amount does nothing for this query, no matter how important amount is elsewhere.

Look for query patterns such as:

• Repeated exact lookups by email, username, external ID, or foreign key
• Range filters over timestamps or numeric values
• Joins on columns that don't already have a useful index
• ORDER BY and LIMIT combinations where returning the first rows quickly matters

Avoid indexing a low-cardinality column by itself unless a common query selects a small fraction of the table. A status column containing only active and inactive may not narrow the search enough to justify a normal index. A partial index fits better when queries repeatedly target a small subset, such as unpaid invoices.

Random identifiers affect index locality and size, so understand the tradeoffs before choosing UUIDs instead of sequential IDs.

Use EXPLAIN Before and After Adding an Index

EXPLAIN shows the optimizer's execution plan. It tells you whether the database scans a table, uses an index, sorts rows, or joins tables in an unexpected order.

EXPLAIN
SELECT id, username
FROM users
WHERE email = '[email protected]';

In PostgreSQL, a Seq Scan reads the table sequentially. An Index Scan, Index Only Scan, or Bitmap Index Scan uses an index. A sequential scan isn't automatically bad; it may be cheapest for a small table or a query that returns many rows.

Use EXPLAIN ANALYZE when you need actual execution counts and timing:

EXPLAIN ANALYZE
SELECT id, amount
FROM transactions
WHERE user_id = 42;

Unlike plain EXPLAIN, EXPLAIN ANALYZE runs the query. Be careful with writes and expensive production queries. Compare estimated and actual row counts, then measure before and after the index change with representative data. A realistic database test beats mocking the database connection.

Composite Index Column Order

A composite index is also called a multi-column index. Multi-column indexes are useful for the exact reason you might think – they speed up lookups that depend on multiple columns.

CREATE INDEX first_name_last_name_age_idx
  ON users (first_name, last_name, age);

A multi-column index is sorted by the first column first, the second column next, and so forth. A lookup on only the first column in a multi-column index gets almost all of the performance improvements that it would get from its own single-column index. However, lookups on only the second or third column will have very degraded performance.

Say we frequently need to look up all the transactions between two specific users – maybe there's a page on the website that allows a user to find all the payments they've made to a friend. An index on the user_id and recipient_id columns speeds that up:

CREATE INDEX user_id_recipient_id_idx
ON transactions (user_id, recipient_id);

PostgreSQL can use this index well when a query constrains the leading column:

SELECT *
FROM transactions
WHERE user_id = 42
  AND recipient_id = 91;

With user_id as the first column, it can also use the index for queries that only care about user_id:

SELECT *
FROM transactions
WHERE user_id = 42;

A query filtering only by recipient_id usually can't use this index as efficiently. Those values aren't globally ordered; they're ordered within each user_id. If that lookup is common, add a separate index with recipient_id first.

Unless you have specific reasons to do something special, only add multi-column indexes if you're doing frequent lookups on a specific combination of columns.

For mixed equality and range conditions, put equality columns first in many common designs:

CREATE INDEX transactions_user_created_idx
ON transactions (user_id, created_at);

That order fits WHERE user_id = ? AND created_at >= ? and can support ORDER BY created_at within one user. It isn't a universal rule. Choose the order based on predicates, sorting, selectivity, and your database's index behavior. PostgreSQL's multicolumn index documentation explains the leftmost-column behavior.

SQL Index Tradeoffs

While indexes make specific kinds of lookups much faster, they also add performance overhead – they can slow down a database in other ways.

Think about it: if you index every column, you could have hundreds of B-trees in memory! That needlessly bloats the memory usage of your database. It also means that each time you insert a record, that record needs to be added to many trees, slowing down your insert speed.

The practical costs include:

• Slower inserts, updates, and deletes
• More disk usage and memory pressure
• Longer schema migrations when building indexes on large tables
• More maintenance for statistics, page splits, vacuuming, or rebuilding

Add indexes for measured query patterns, then remove redundant or unused ones. Similar composite indexes may overlap, and an index that helped last year's workload may be dead weight today.

When Should You Denormalize for Speed?

Try indexes before denormalization. As it turns out, data integrity and deduplication come at a cost, and that cost is usually speed.

Joining tables together, using subqueries, performing aggregations, and running post-hoc calculations take time. At very large scales these advanced techniques can actually become a huge performance toll on an application – sometimes grinding the database server to a halt.

Denormalization – storing duplicate information – can drastically speed up an application that needs to look it up in different ways. For example, if you store a user's country information right on their user record, no expensive join is required to load their profile page!

That said, denormalize at your own risk! Denormalizing a database incurs a large risk of inaccurate and buggy data – a country correction may now require updating many rows, and failed updates can leave contradictory data. In my opinion, it should be used as a kind of "last resort" in the name of speed.

Normalize the schema first. Inspect slow queries with EXPLAIN, add indexes that match the workload, and measure again. Denormalize only when those fixes miss a concrete performance requirement.