Database Indexing Explained: How Queries Get Faster

When a query on a large table feels instant, an index is usually doing the work. When the same query crawls, an index is often missing, unused, or poorly designed. Database indexing is one of the highest-leverage ideas in backend engineering: it changes how the database finds rows without changing the meaning of your SQL.

This guide explains what an index is, how common index structures work internally, when they help, when they hurt, and how to reason about them as a developer. The ideas apply to PostgreSQL, MySQL/InnoDB, SQLite, SQL Server, and most other relational systems, even though storage details differ.

Why Developers Should Understand Indexes

Applications spend most of their waiting time on I/O and data lookup. An unindexed filter on a million-row table can scan every row. An index can jump to the matching keys and follow pointers to the rows you need.

Indexes show up everywhere real systems run:

• Looking up a user by email during login
• Fetching an order by order ID
• Listing posts for one author, newest first
• Enforcing uniqueness on usernames
• Joining tables on foreign keys

If you only write SQL that is logically correct, you can still ship a product that collapses under load. Indexes are how logical correctness becomes operational speed.

Simple Explanation

A table without a useful index is like a book with no table of contents and no back-of-book index. To find every mention of a word, you read the whole book.

A database index is a separate, sorted structure the engine maintains alongside the table. It stores key values and locations of matching rows. When you filter or join on those keys, the engine can search the index instead of reading every row.

• Reads that match the index become much cheaper.
• Writes become a bit more expensive, because each insert, update, or delete must also update the index.
• Indexes take extra disk and memory.
• The query planner decides whether an index is worth using. Creating an index does not guarantee it will be used.

How Indexing Works Internally

Table pages plus secondary structures

Most engines store table rows in pages (often 8 KB). A page is the unit of disk I/O. Finding one row by scanning means reading many pages you do not need.

An index is another set of pages organized so the engine can search to a key. Each leaf entry points at the row: a row identifier in a heap table, or the clustered key in a clustered table such as InnoDB.

Application
 |
 v
SQL query (WHERE / JOIN / ORDER BY)
 |
 v
Query planner
 |
 +-- Sequential scan of table pages
 |
 +-- Index search
      |
      v
   Index pages (root -> internal -> leaf)
      |
      v
   Row pointers / clustered key
      |
      v
   Table pages with the actual columns

B-tree indexes (the default)

The workhorse index in relational databases is a B-tree (or B+ tree). Keys stay sorted. Internal nodes route the search. Leaf nodes hold keys and row pointers, often linked so range scans can walk left to right.

Lookup for a single key is roughly proportional to the height of the tree. For millions of rows the height is typically three or four levels. That is why a point lookup can cost a handful of page reads instead of hundreds of thousands.

Range queries such as created_at greater-than a timestamp or id BETWEEN two values fit B-trees well because neighboring keys live near each other in the leaves.

Clustered vs secondary indexes

In InnoDB, the table itself is organized as a clustered index on the primary key. Secondary indexes store the secondary key plus the primary key, then a second lookup finds the full row.

In PostgreSQL, a standard table is a heap. Indexes are secondary structures pointing at heap tuple identifiers. After an index hit, the engine still fetches the heap page unless an index-only scan can answer the query from the index and visibility map.

This difference matters when you choose a primary key. A wide, random UUID as a clustered primary key can scatter inserts and bloat secondary indexes. A compact sequential key often produces tighter pages.

Hash indexes

A hash index maps a key to a bucket. Equality lookups can be very fast. Range scans are not supported, because hash buckets are not ordered. Many engines limit hash indexes or use hashing internally for joins rather than as the default user-facing type.

What the planner actually does

The planner estimates cost using table statistics: row counts, most-common values, histograms, and null fractions. It compares sequential scan cost, index scan plus random row fetches, bitmap index scan for many matches, and index-only scan when all needed columns live in the index.

If a predicate matches 80 percent of the table, a sequential scan is often cheaper than jumping around with an index. Selectivity is the reason adding an index sometimes changes nothing.

Real-World Examples

Login by email

A users table with millions of rows and WHERE email = $1 needs an index on email. That index is also the natural place to enforce uniqueness so two accounts cannot share an address.

Foreign keys and joins

Listing all orders for a customer is WHERE customer_id = $1. Without an index on orders.customer_id, every order row is read. With the index, only that customer's order pointers are followed. The same index helps nested-loop joins from customers to orders.

Feed queries

Posts for one user ordered by time want an index that matches both filter and sort, for example (user_id, created_at DESC). That composite index can support the filter and avoid a separate sort.

Admin search that should not use a normal index

WHERE lower(name) LIKE '%term%' cannot use a normal B-tree on name. A leading wildcard prevents an ordered search from starting at a prefix. Teams solve this with full-text search, trigram indexes, or a dedicated search engine.

Code Examples

Assume a simplified orders table.

CREATE TABLE orders (
  id           BIGSERIAL PRIMARY KEY,
  customer_id  BIGINT NOT NULL,
  status       TEXT NOT NULL,
  total_cents  INTEGER NOT NULL,
  created_at   TIMESTAMPTZ NOT NULL DEFAULT now()
);

The primary key already gives you a unique B-tree on id. Lookups by customer still scan the table until you add:

CREATE INDEX orders_customer_id_idx
  ON orders (customer_id);

A typical list query:

SELECT id, status, total_cents, created_at
FROM orders
WHERE customer_id = 42
ORDER BY created_at DESC
LIMIT 20;

A composite index matches the filter and the sort:

CREATE INDEX orders_customer_created_idx
  ON orders (customer_id, created_at DESC);

If the UI only needs those columns, a covering index can enable index-only scans in engines that support them:

CREATE INDEX orders_customer_created_cover_idx
  ON orders (customer_id, created_at DESC)
  INCLUDE (status, total_cents);

Always inspect the plan. In PostgreSQL:

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, status, total_cents, created_at
FROM orders
WHERE customer_id = 42
ORDER BY created_at DESC
LIMIT 20;

Look for Index Scan or Index Only Scan on your new index, a reasonable row estimate, and a low actual time. Seq Scan on a large table with a selective customer_id is a warning sign.

Leftmost prefix rule

A B-tree on (customer_id, created_at) can support WHERE customer_id = 42, that filter plus a created_at range, and that filter plus ORDER BY created_at. It cannot efficiently support WHERE created_at > $1 alone, because the tree is not ordered by created_at first. Column order in a composite index is a design decision, not decoration.

Common Misconceptions

More indexes always make the database faster. Each extra index slows writes and consumes cache. Unused indexes are dead weight.

An index on every column is safe. Columns that are almost never filtered, or that have only two values with even distribution, rarely deserve a standalone index. A status column that is active for 95 percent of rows will not help most WHERE status = 'active' queries.

Primary key is enough for all lookups. The primary key helps lookups by that key. Foreign keys, emails, slugs, and sort columns need their own indexes if those access paths matter.

Functions in WHERE still use the column index. WHERE LOWER(email) = $1 will not use a plain index on email unless you create an expression index on LOWER(email) or store a normalized value.

NULL cannot be indexed. B-trees can store NULLs. Whether IS NULL uses the index depends on the engine and statistics.

Indexes replace good queries. Selecting unused columns, fetching millions of rows into the app, or running N+1 queries will stay slow even with perfect indexes.

Best Practices and Key Takeaways

• Index for access paths you actually run: equality filters, joins, uniqueness, and common ORDER BY plus LIMIT patterns.
• Prefer composite indexes that match the query's filter order and sort order over a pile of single-column indexes.
• Keep indexed columns stable and selective when you can. High-churn columns increase write amplification.
• Normalize values you search so you index the same form you query.
• Use EXPLAIN ANALYZE on slow queries before adding indexes, and again after.
• Drop indexes that the planner never uses in production, after you confirm with usage stats.
• Rebuild or vacuum according to your engine so bloat does not turn a good index into random I/O.
• Treat the primary key as a clustering decision in engines like InnoDB, not only as a uniqueness constraint.

The mental model to keep: an index is a maintained sorted map from keys to rows. You pay to keep the map current so reads can stop scanning the whole table.

FAQ

What is a database index in simple terms?

It is a separate sorted structure that lets the database find rows by key without reading every row in the table.

Does every table need indexes?

Tiny tables often do not benefit. Large tables that are filtered, joined, or sorted on specific columns usually do. Primary keys already create one unique index in most engines.

Why is my new index not being used?

The predicate may not match the index columns, the planner may estimate that a sequential scan is cheaper, statistics may be stale, or a function wrapping the column may hide the index.

What is the difference between a clustered index and a secondary index?

A clustered index is the table stored in key order. A secondary index is an extra structure that points into the table or to the clustered key.

Are indexes used for ORDER BY?

Yes, if the index order matches the requested sort and the query can start at the right position in the leaves. That is how LIMIT queries avoid sorting the whole result.

Do indexes slow down INSERT and UPDATE?

Yes. Each write must maintain every index that includes a changed key. That is the tradeoff for faster reads.

What is a covering index?

An index that contains every column the query needs, so the engine can answer without visiting the heap or clustered row.

Should I index foreign key columns?

In most applications, yes. Joins and filters on child.fk = parent.id are among the most common indexed access paths.

Related Articles

• How Databases Work Internally
• How HTTP Works
• How HTTPS Works
• How Cookies and Sessions Work
• What Happens When You Type a URL