Backend Study Lab

System Design

Database Indexes Explained

Learn how database indexes work, why they speed up reads, when they slow down writes, and how backend developers should choose indexes.

By CloveCode Studio Jun 28, 2026 Updated Jun 28, 2026 4 min read

Quick answer

A database index is a data structure that helps the database find rows without scanning the whole table. The common mental model is a sorted lookup structure that points from indexed values to row locations.

Indexes make many read queries faster, but they are not free. They take storage, slow down writes, and must be chosen based on real query patterns.

Why indexes matter

Imagine a table with millions of users:

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

Without an index on email, the database may need to inspect many rows to find the match. With an index, it can jump through the index structure and find the row much faster.

Indexes are one of the most common backend performance tools because they sit close to the data and improve query latency without changing the API contract.

A simple index example

For this query:

SELECT id, email, created_at
FROM users
WHERE email = 'alice@example.com';

An index like this is often useful:

CREATE INDEX idx_users_email ON users (email);

The database can use idx_users_email to locate rows by email instead of scanning the entire users table.

The exact syntax and planner behavior vary by database, but the principle is similar across common relational systems.

B-tree indexes

Many relational databases use B-tree or B-tree-like indexes for general-purpose lookup.

B-tree indexes are useful for:

  • Equality lookups such as email = ?.
  • Range queries such as created_at >= ?.
  • Sorting when the ORDER BY matches the index.
  • Prefix matches in some database-specific cases.

This is why a single-column index on created_at can help queries that filter recent rows or sort by creation time.

Composite indexes

A composite index covers multiple columns:

CREATE INDEX idx_orders_account_status_created
ON orders (account_id, status, created_at);

This can help a query like:

SELECT *
FROM orders
WHERE account_id = 42
  AND status = 'paid'
ORDER BY created_at DESC;

Column order matters. A composite index is not just three independent indexes bundled together. It is ordered by the first column, then the second, then the third.

The leftmost prefix rule

With an index on (account_id, status, created_at), the database can commonly use it for query patterns that start from the left:

Query filterLikely index fit
account_id = ?Good
account_id = ? AND status = ?Good
account_id = ? AND status = ? ORDER BY created_atGood
status = ? onlyUsually not a good fit
created_at > ? onlyUsually not a good fit

This is a useful rule of thumb, not a replacement for reading the actual query plan.

Indexes and sorting

Indexes can help with ORDER BY when the index order matches the query.

For example, a feed query might use:

CREATE INDEX idx_posts_account_created
ON posts (account_id, created_at DESC);

Then this query has a better chance of avoiding an expensive sort:

SELECT id, title, created_at
FROM posts
WHERE account_id = 42
ORDER BY created_at DESC
LIMIT 20;

This is especially important for pagination. Read Pagination Strategies for REST APIs for API-level pagination tradeoffs.

Indexes are not free

Every index adds cost:

CostWhy it matters
StorageThe index stores extra data and row references.
Insert costNew rows must be added to each relevant index.
Update costChanging indexed columns updates the index too.
Delete costDeleted rows must be removed or marked in indexes.
Planner complexityToo many overlapping indexes can make maintenance harder.

This is why “add an index to every column” is not a good strategy.

Covering indexes

A covering index contains all the columns needed by a query, so the database may be able to answer from the index without reading the table row.

For example:

CREATE INDEX idx_users_email_id_created
ON users (email, id, created_at);

This can cover:

SELECT id, created_at
FROM users
WHERE email = 'alice@example.com';

Covering indexes can be powerful, but they also make indexes larger. Use them for frequent, latency-sensitive queries.

Common indexing mistakes

The first mistake is adding indexes without looking at queries. Indexes should follow access patterns: filters, joins, sorting, grouping, and pagination.

The second mistake is ignoring write-heavy tables. A table that receives many inserts or updates can become slower when it has too many indexes.

The third mistake is indexing low-cardinality columns alone. A column like status with only a few values may not filter enough rows by itself, though it can still be useful inside a composite index.

The fourth mistake is assuming the database used an index because one exists. Always inspect the query plan for important queries.

Backend checklist

When reviewing a slow query:

  • Identify the exact WHERE, JOIN, ORDER BY, and LIMIT shape.
  • Check table size and expected result size.
  • Inspect the query plan.
  • Add or adjust an index for the real access pattern.
  • Re-run the query plan and measure latency.
  • Remove unused or redundant indexes over time.

Backend performance usually comes from matching data structure to access pattern. Indexes are the database version of that idea.

Read N+1 Query Problem Explained for query-count problems that indexes alone cannot fix, and Database Connection Pooling Explained for the application-side pressure around slow queries. For API design around sorted result sets, read Pagination Strategies for REST APIs. Use the SQL Formatter when cleaning up queries during index review, and browse the System Design Learning Path for the broader database performance sequence.

Keep reading