Imagine your users table has 10 million rows.
Your application frequently runs:
SELECT *
FROM users
WHERE email = 'alex@example.com';
The database returns the result, but how did it find one user among millions?
It could check the rows one by one.
Or, it could use an index.
An index gives the database a more efficient way to find data without scanning the entire table every time.
But indexes aren't magic. They consume storage, add overhead to writes, and aren't useful for every query.
Understanding that trade-off is what makes indexing useful from a developer's perspective.
1. What Is a Database Index?
A database index is an additional data structure that helps the database locate rows more efficiently.
Think about a book.
If you want to find a particular topic, you don't normally read every page. You use the index to find where that topic appears.
A database index follows a similar idea:
Without Index
Query
↓
Scan rows
↓
Find matching rows
↓
Return result
With an appropriate index:
Query
↓
Search Index
↓
Locate relevant rows
↓
Read required data
↓
Return result
The important idea is:
An index can reduce the amount of data the database needs to examine.
That's the real reason indexes can make queries faster.
2. What Happens Without an Index?
Suppose we have:
users
id name email
-----------------------------
1 Rahul rahul@mail.com
2 Priya priya@mail.com
3 Alex alex@mail.com
4 John john@mail.com
...
Now run:
SELECT *
FROM users
WHERE email = 'alex@mail.com';
If there isn't a useful index on email, the database may perform a sequential scan.
Conceptually:
Row 1 → Check
Row 2 → Check
Row 3 → Match
Row 4 → Check
...
With a small table, this isn't a big deal.
But imagine the table contains:
10,000 rows
1 million rows
100 million rows
Scanning a large portion of the table for every lookup can become expensive.
3. What Changes When We Add an Index?
We can create an index:
CREATE INDEX idx_users_email
ON users(email);
Now the database has another structure that can help it search for email values.
Conceptually:
Query
↓
Email Index
↓
alex@mail.com
↓
Row location
↓
User row
Instead of searching through potentially millions of table rows, the database can first search the much more structured index.
That's the fundamental advantage.
The database isn't necessarily processing the same amount of data faster.
It's potentially processing much less data.
4. How Does an Index Search So Quickly?
Many traditional database indexes use tree-based structures, particularly B-tree-style indexes.
You can think about a simplified tree like this:
50
/ \
25 75
/ \ / \
10 40 60 90
If you're searching for 60, you don't need to inspect every value.
You start at the top:
60 > 50
↓
Go right
60 < 75
↓
Go left
60 found
Real database indexes are much more sophisticated and optimized for storage systems, but the underlying idea is useful:
Organize values so large portions of the search space can be eliminated quickly.
5. Indexes Usually Don't Store the Whole Table
An index isn't simply a second copy of your table.
Conceptually, an index might contain:
email → row location
alex@mail.com → location
john@mail.com → location
priya@mail.com → location
rahul@mail.com → location
The exact structure depends on the database and index type.
The index provides information that helps the database locate the underlying data.
So you can think of your database as:
Database
|
├── Table
|
└── Index
The table stores the actual records.
The index provides an additional access path to those records.
6. An Index Doesn't Guarantee Faster Queries
This is an important detail.
Suppose your table contains only 20 rows.
You create an index:
CREATE INDEX idx_users_email
ON users(email);
The database might still choose a table scan.
Why?
Because scanning 20 rows could be cheaper than using the index and then fetching the corresponding table rows.
The database's query optimizer decides which execution strategy appears cheaper.
Conceptually:
Query
↓
Query Optimizer
↓
Evaluate possible plans
↓
Choose execution plan
So:
Creating an index gives the database an option. It doesn't force the database to use it.
7. How Do You Know What the Database Is Doing?
Instead of guessing, inspect the execution plan.
For example, PostgreSQL supports:
EXPLAIN
SELECT *
FROM users
WHERE email = 'alex@example.com';
You can also inspect actual execution behavior with:
EXPLAIN ANALYZE
SELECT *
FROM users
WHERE email = 'alex@example.com';
This can help you understand:
Was an index used?
Was a sequential scan performed?
How many rows were expected?
How many rows were actually processed?
How much time did the operation take?
This is much more useful than simply assuming an index is helping.
8. Indexes Are a Trade-Off
If indexes only had benefits, we'd put them on every column.
But they don't.
Indexes require additional:
- Storage
- Memory
- Write work
- Maintenance
Suppose you insert a new user:
INSERT INTO users
(name, email)
VALUES
('Alex', 'alex@example.com');
The database needs to update the table.
If email is indexed, it also needs to maintain that index.
Conceptually:
INSERT
↓
Update Table
↓
Update Index
If a table has many indexes, writes may require more index maintenance.
So indexing creates a trade-off:
Faster Reads
↕
More Write Overhead
Good database design is about finding the right balance.
9. Which Columns Should You Index?
Don't start by looking at the schema and saying:
"This column looks important. Let's index it."
Start with your queries.
Suppose your application frequently executes:
SELECT *
FROM users
WHERE email = ?;
An index on email could be useful.
If it frequently executes:
SELECT *
FROM orders
WHERE user_id = ?;
an index on user_id may be worth considering.
If it frequently executes:
SELECT *
FROM products
ORDER BY created_at DESC
LIMIT 20;
an index involving created_at might help.
The key question is:
How does the application actually access the data?
Indexes should follow real access patterns.
10. Primary Keys and Indexes
Primary keys are common candidates for efficient lookups.
For example:
CREATE TABLE users (
id BIGINT PRIMARY KEY,
name VARCHAR(100),
email VARCHAR(255)
);
A database generally creates or enforces an index-like structure associated with the primary key, depending on the database system.
That makes queries such as:
SELECT *
FROM users
WHERE id = 42;
a natural use case for indexed access.
This is one reason primary keys are so commonly used for direct record retrieval.
11. Unique Indexes
Indexes can also help enforce data integrity.
Suppose every user must have a unique email:
CREATE UNIQUE INDEX idx_users_email
ON users(email);
Now the index isn't only useful for finding users.
It also helps enforce the rule that two users cannot have the same email value under that uniqueness constraint.
So an index can contribute to both:
Performance
+
Data Integrity
12. Composite Indexes
Real applications often filter using multiple columns.
Consider:
SELECT *
FROM orders
WHERE user_id = 42
AND status = 'completed';
You might consider:
CREATE INDEX idx_orders_user_status
ON orders(user_id, status);
This is a composite index.
It indexes a combination of columns rather than just one.
Conceptually:
(user_id, status)
Composite indexes can be extremely useful when they match common query patterns.
But there's an important detail.
13. Column Order Matters
Consider:
CREATE INDEX idx_orders_user_status
ON orders(user_id, status);
The index is organized around:
user_id
↓
status
A query using:
WHERE user_id = 42
aligns naturally with the leading column.
A query using only:
WHERE status = 'completed'
is a different situation.
This is why composite indexes should be designed based on the queries they are intended to support.
Don't simply combine every column that appears in your queries.
Think about the actual access patterns.
14. Indexes Can Help With Sorting
Indexes aren't only useful for WHERE conditions.
Consider:
SELECT *
FROM products
ORDER BY created_at DESC
LIMIT 20;
Without a suitable access path, the database may need to:
Read rows
↓
Sort rows
↓
Return 20
With an appropriate index, the database may be able to access rows in a useful order.
Conceptually:
Index
↓
Rows already accessible in useful order
↓
Return required rows
Whether the database actually chooses this strategy depends on the query and execution plan.
15. Indexes Can Help With Range Queries
Indexes are also useful for many range queries.
For example:
SELECT *
FROM products
WHERE price >= 1000
AND price <= 5000;
An ordered index on price can help locate the relevant range.
Conceptually:
Price Index
500
1000 ← Start
1500
2000
3000
4000
5000 ← End
6000
Instead of searching the entire table, the database can navigate toward the relevant part of the index.
This is useful for things such as:
Prices
Dates
Timestamps
Numeric values
IDs
16. Not Every Query Benefits From an Index
Consider:
SELECT *
FROM users
WHERE status = 'active';
Suppose the table contains 10 million users and 9 million are active.
An index on status may not provide much benefit for this particular query because most of the table matches anyway.
The database may decide that scanning the table is more efficient.
This is related to selectivity.
A condition is highly selective when it narrows the possible rows significantly.
For example:
email = 'alex@example.com'
might match one row.
While:
status = 'active'
might match millions.
But don't treat selectivity as an absolute rule.
The optimizer considers many factors when choosing a plan.
17. Indexes Don't Fix Bad Queries
Suppose you create an index on:
user_id
and then execute:
SELECT *
FROM orders
WHERE user_id = 42;
What if user 42 has 10 million orders?
The index can help locate those orders, but the database still has to process and return a huge result.
The problem isn't necessarily the missing index anymore.
The query itself may be asking for too much data.
A better API might use:
SELECT id, total, created_at
FROM orders
WHERE user_id = 42
ORDER BY created_at DESC
LIMIT 50;
Indexing and query design need to work together.
18. Indexes and Pagination
This becomes particularly important for APIs.
A common pagination approach is:
SELECT *
FROM products
ORDER BY id
LIMIT 20 OFFSET 20000;
For large offsets, the database may still need to process or skip many preceding rows.
An alternative is cursor or keyset pagination:
SELECT *
FROM products
WHERE id > 20000
ORDER BY id
LIMIT 20;
With a suitable index, the database can start from a known position.
This is a good example of a broader lesson:
Performance comes from good query design plus appropriate indexing.
An index isn't a substitute for understanding how your data is accessed.
19. Too Many Indexes Can Become a Problem
Imagine this table:
users
id
name
email
phone
age
city
status
created_at
You might create an index on every column.
It sounds optimized.
But now every relevant write may require maintaining several index structures.
You also consume more storage.
And some indexes might never be used by important queries.
So the goal isn't:
Maximum Indexes
It's:
Useful Indexes
A good index is one that solves a real access problem.
20. A Practical Example
Imagine an e-commerce application.
The orders table contains:
50 million rows
The application frequently runs:
SELECT id, total, created_at
FROM orders
WHERE user_id = ?
ORDER BY created_at DESC
LIMIT 20;
As the table grows, the endpoint becomes slower.
Instead of immediately adding more backend servers, you inspect the database execution plan.
You discover that the database is doing far more work than necessary.
Now you can evaluate whether an index designed around:
user_id
created_at
matches the query pattern.
After making the change, you check the execution plan again and benchmark the query.
That's the right way to approach indexing.
Not:
"The query is slow, so add an index."
But:
Slow Query
↓
Inspect Plan
↓
Understand the Work
↓
Design Appropriate Index
↓
Measure Again
21. Indexing Is Part of System Design
Database indexing might look like a small implementation detail.
But database performance affects the entire application.
Consider:
User
↓
API Request
↓
Backend
↓
Database Query
↓
Slow Scan
If the query becomes slower:
Database latency increases
↓
API latency increases
↓
Requests stay active longer
↓
More concurrent requests
↓
Higher resource usage
A well-designed index can sometimes reduce that chain by making the database work more efficiently.
That's why database optimization is part of system design.
A Simple Mental Model
When you see a slow query, don't immediately think:
"I need an index."
Instead ask:
What is the database doing?
↓
Is it scanning too much data?
↓
Can an index provide a better access path?
↓
Does the query itself need improvement?
↓
What does EXPLAIN show?
↓
Did performance actually improve?
And remember the fundamental trade-off:
INDEX
|
┌─────────┴─────────┐
↓ ↓
Faster Reads Additional Cost
|
┌────────┼────────┐
↓ ↓ ↓
Storage Writes Maintenance
The biggest misconception about indexing is that an index simply makes the database "search faster."
A better way to think about it is:
An index gives the database a more efficient access path to the data.
If that access path matches the query, the database may avoid scanning, sorting, or processing large amounts of unnecessary data.
But every index comes with a cost.
So good indexing isn't about creating indexes everywhere.
It's about understanding your application's most important queries, inspecting how the database executes them, and creating indexes that reduce expensive work without creating unnecessary overhead elsewhere.
The best index isn't the one you can create. It's the one your workload actually needs.
Top comments (0)