Understand how database indexes work, when to use them, and how to read query plans to keep performance predictable as data grows.
Database performance often becomes a critical concern as applications grow. A query that works perfectly with a few thousand rows can become unexpectedly slow when the database contains millions of records.
One of the most important tools for improving query performance is the database index.
Indexes can dramatically reduce the amount of data a database needs to scan, but they also introduce storage and write overhead. Understanding when and how to use them is therefore essential for backend developers and software engineers.
An index is a data structure that helps the database find rows more efficiently.
Consider a table containing millions of users:
CREATE TABLE users (
id BIGINT PRIMARY KEY,
name VARCHAR(255),
email VARCHAR(255),
created_at TIMESTAMP
);
Suppose the application frequently executes:
SELECT *
FROM users
WHERE email = 'jane@example.com';
Without an appropriate index, the database may need to inspect a large number of rows to find the matching email.
Creating an index can make this lookup significantly faster:
CREATE INDEX idx_users_email
ON users(email);
Instead of scanning the entire table, the database can use the index to locate the relevant rows more efficiently.
Indexes improve read performance, but they have costs.
An index requires:
If a table has many indexes, every write may require updating several index structures.
For this reason, adding an index to every column is usually a bad strategy.
The goal is to create indexes that support real query patterns.
A good starting point is to identify columns frequently used in:
WHEREJOINORDER BYGROUP BYFor example:
SELECT *
FROM orders
WHERE customer_id = 42;
An index on customer_id may improve this query:
CREATE INDEX idx_orders_customer_id
ON orders(customer_id);
Similarly, if an application frequently searches users by email, indexing the email column is usually appropriate.
Sometimes queries filter by multiple columns.
Consider:
SELECT *
FROM orders
WHERE customer_id = 42
AND status = 'pending';
A composite index can support this query:
CREATE INDEX idx_orders_customer_status
ON orders(customer_id, status);
The order of columns in a composite index matters.
For example:
(customer_id, status)
is not equivalent to:
(status, customer_id)
The optimal order depends on how the index is used by the application's queries.
A useful starting point is to place columns used in highly selective filtering or common query prefixes appropriately, then verify the result with the database query planner.
Selectivity describes how effectively a column narrows down a result set.
Consider a table containing one million users.
A column such as:
country
might contain only a few hundred distinct values.
A column such as:
email
is likely to contain close to one million distinct values.
Searching for a specific email can therefore eliminate far more rows than searching for a common country.
This does not mean low-cardinality columns should never be indexed. The usefulness of an index depends on the complete query, data distribution, workload, and database engine.
The important principle is to evaluate indexes against real workloads rather than relying on simplistic rules.
One of the most useful tools for understanding query performance is EXPLAIN.
For example:
EXPLAIN
SELECT *
FROM orders
WHERE customer_id = 42;
Depending on the database engine, the output can reveal information such as:
Some databases also provide an execution-analysis variant, such as:
EXPLAIN ANALYZE
SELECT *
FROM orders
WHERE customer_id = 42;
This can provide actual execution information rather than only estimates.
Always be careful when running execution-analysis commands against production databases, especially for expensive queries.
Performance problems do not always come from a single slow query.
Consider an application that loads 100 users:
1 query → fetch users
100 queries → fetch orders for each user
The application ends up executing 101 queries.
This is commonly known as the N+1 query problem.
Depending on the use case, the solution may involve:
For example:
SELECT users.id, users.name, orders.id
FROM users
LEFT JOIN orders
ON orders.user_id = users.id;
The best solution depends on the amount of data and the application's access patterns.
Using:
SELECT *
FROM users;
is convenient, but it can be problematic in production applications.
If a table contains many columns, the application may retrieve significantly more data than necessary.
Instead, select the fields that are actually required:
SELECT id, name, email
FROM users;
This can reduce:
It also makes the query's intent clearer.
A query that returns thousands or millions of rows can become expensive even when it uses an index.
Instead of:
SELECT *
FROM orders
ORDER BY created_at DESC;
use pagination.
A simple approach is:
SELECT *
FROM orders
ORDER BY created_at DESC
LIMIT 50
OFFSET 100;
For very large datasets, cursor-based or keyset pagination can be more efficient.
For example:
SELECT *
FROM orders
WHERE id < 1000
ORDER BY id DESC
LIMIT 50;
This avoids asking the database to skip a potentially large number of rows.
Indexes can also help with ordering.
Consider:
SELECT *
FROM orders
WHERE customer_id = 42
ORDER BY created_at DESC;
A composite index such as:
CREATE INDEX idx_orders_customer_created
ON orders(customer_id, created_at DESC);
may allow the database to efficiently locate the customer's orders while also benefiting from the index ordering.
However, whether this is beneficial depends on the database engine and the exact query plan.
Always verify with EXPLAIN.
A common mistake is creating indexes based solely on intuition.
Before optimizing a query, establish a baseline.
Measure:
Then make one change and measure again.
For example:
Before:
Query time: 850 ms
After adding index:
Query time: 12 ms
This gives you evidence that the optimization actually helped.
The opposite can also happen. An index may provide little or no benefit while increasing write costs and storage usage.
Database indexes are one of the most powerful tools available for improving query performance, but they should be used deliberately.
Start by understanding the application's actual query patterns. Index columns that support important access paths, use composite indexes when appropriate, and inspect query plans with tools such as EXPLAIN.
Performance optimization should always be based on measurement.
A fast query is valuable, but a database architecture that remains predictable as data and traffic grow is even more valuable.
You are in reading mode. Open the discussions tab to explore threads about this article.