You've probably experienced this.
Your app works perfectly during development.
Then production arrives.
A table grows from:
1,000 rows
to:
10,000,000 rows
Suddenly:
- pages load slowly
- APIs time out
- users complain
- CPUs start screaming
The SQL query hasn't changed.
So what happened?
In many cases, the answer is surprisingly simple:
You're missing an index.
Index
- The Mystery of the Slow Query
- What Is a Database Index?
- A Real-Life Analogy
- How Databases Search Without an Index
- How Databases Search With an Index
- What Is a B-Tree?
- Creating Your First Index
- The Performance Difference
- Single vs Composite Indexes
- The Leftmost Prefix Rule
- When Indexes Can Hurt Performance
- Common Indexing Mistakes
- How to Know if an Index Is Being Used
- Indexes in PostgreSQL and MySQL
- Real-World Examples
- Final Thought
1. The Mystery of the Slow Query
Imagine this query:
SELECT *
FROM users
WHERE email = 'john@example.com';
Looks harmless.
But if your database contains:
10 million users
the database might need to check:
Row 1
Row 2
Row 3
...
Row 10,000,000
until it finds the match.
That's expensive.
2. What Is a Database Index?
A database index is a special data structure that helps the database find data quickly.
Think of it as:
A shortcut to your data.
Instead of scanning every row, the database jumps directly to the relevant records.
3. A Real-Life Analogy
Imagine you're looking for:
"Distributed Systems"
inside a 1,000-page book.
Without an Index
You start from page 1.
Page 1
Page 2
Page 3
...
Page 1000
Eventually you find it.
Very slow.
With an Index
You open the index page:
Distributed Systems → Page 742
Done.
That's exactly what a database index does.
4. How Databases Search Without an Index
Without an index:
SELECT *
FROM users
WHERE email = 'john@example.com';
The database performs:
Full Table Scan
User 1
User 2
User 3
User 4
...
User 10,000,000
Every row is inspected.
This becomes painful as data grows.
5. How Databases Search With an Index
With an index:
CREATE INDEX idx_users_email
ON users(email);
The database now maintains a searchable structure.
Instead of:
Scan everything
it can:
Jump directly to matching rows
Result:
Milliseconds instead of seconds
6. What Is a B-Tree?
Most database indexes use a structure called a B-Tree.
A simplified example:
[M]
/ \
[F] [T]
/ \ / \
A-D G-L N-S U-Z
Instead of searching every record:
1
2
3
4
...
the database repeatedly narrows the search space.
Much faster.
7. Creating Your First Index
Suppose:
CREATE TABLE users (
id SERIAL PRIMARY KEY,
name TEXT,
email TEXT
);
Create an index:
CREATE INDEX idx_users_email
ON users(email);
Now email lookups become dramatically faster.
8. The Performance Difference
Imagine:
10 million users
Without index:
2–10 seconds
With index:
1–20 milliseconds
That's why indexes are often the first thing engineers check when debugging slow queries.
9. Single vs Composite Indexes
Single Column Index
CREATE INDEX idx_users_email
ON users(email);
Optimized for:
WHERE email = ?
Composite Index
CREATE INDEX idx_users_country_status
ON users(country, status);
Optimized for:
WHERE country = ?
AND status = ?
10. The Leftmost Prefix Rule
One of the most important concepts.
Given:
(country, status)
The index can help with:
WHERE country = 'US'
or:
WHERE country = 'US'
AND status = 'ACTIVE'
But often not:
WHERE status = 'ACTIVE'
because the search starts from the leftmost column.
This catches many developers by surprise.
11. When Indexes Can Hurt Performance
Indexes aren't free.
Every time you:
INSERT
UPDATE
DELETE
the index must also be updated.
More indexes means:
- more storage
- slower writes
- higher maintenance cost
A common mistake is:
Indexing everything.
12. Common Indexing Mistakes
Mistake #1
Indexing tiny tables.
A table with:
100 rows
doesn't need fancy indexing.
Mistake #2
Too many indexes.
Every write becomes slower.
Mistake #3
Wrong column order.
(country, status)
is not the same as:
(status, country)
Mistake #4
Never checking query plans.
Many developers assume indexes are being used.
Sometimes they're not.
13. How to Know if an Index Is Being Used
In PostgreSQL:
EXPLAIN ANALYZE
SELECT *
FROM users
WHERE email = 'john@example.com';
You'll see something like:
Index Scan
or:
Seq Scan
If you see:
Seq Scan
the database is scanning rows sequentially.
Usually not ideal for large tables.
14. Indexes in PostgreSQL and MySQL
Both support:
- B-Tree indexes
- Unique indexes
- Composite indexes
PostgreSQL also offers advanced types:
- Partial Indexes
- Expression Indexes
- GIN Indexes
- GiST Indexes
Useful for:
- full-text search
- JSON queries
- arrays
- geospatial data
15. Real-World Examples
Login Systems
WHERE email = ?
Index email.
E-Commerce
WHERE product_id = ?
Index product IDs.
Social Media
WHERE user_id = ?
ORDER BY created_at DESC
Use a composite index:
(user_id, created_at)
Banking
WHERE account_number = ?
Always indexed.
16. Final Thought
Database indexes are one of the highest-impact performance tools in software engineering.
They don't change your application code.
They don't require more servers.
They don't require microservices.
Yet they can turn:
10 seconds
into:
10 milliseconds
Understanding indexes is often the difference between:
A database that survives millions of users
and
A database that collapses under its own growth.
And the best part?
Most performance problems start with a single question:
"Do we have the right index for this query?"