Database Indexes Explained Simply: Why Some Queries Take 1ms and Others Take 10 Seconds

You’ve probably experienced this.

Your app works perfectly during development.

Then production arrives.

A table grows from:

1,000 rows

Enter fullscreen mode Exit fullscreen mode

to:

10,000,000 rows

Enter fullscreen mode Exit fullscreen mode

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

1. The Mystery of the Slow Query
2. What Is a Database Index?
3. A Real-Life Analogy
4. How Databases Search Without an Index
5. How Databases Search With an Index
6. What Is a B-Tree?
7. Creating Your First Index
8. The Performance Difference
9. Single vs Composite Indexes
10. The Leftmost Prefix Rule
11. When Indexes Can Hurt Performance
12. Common Indexing Mistakes
13. How to Know if an Index Is Being Used
14. Indexes in PostgreSQL and MySQL
15. Real-World Examples
16. Final Thought

1. The Mystery of the Slow Query

Imagine this query:

SELECT *
FROM users
WHERE email = '[email protected]';

Enter fullscreen mode Exit fullscreen mode

Looks harmless.

But if your database contains:

10 million users

Enter fullscreen mode Exit fullscreen mode

the database might need to check:

Row 1
Row 2
Row 3
...
Row 10,000,000

Enter fullscreen mode Exit fullscreen mode

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"

Enter fullscreen mode Exit fullscreen mode

inside a 1,000-page book.

Without an Index

You start from page 1.

Page 1
Page 2
Page 3
...
Page 1000

Enter fullscreen mode Exit fullscreen mode

Eventually you find it.

Very slow.

With an Index

You open the index page:

Distributed Systems → Page 742

Enter fullscreen mode Exit fullscreen mode

Done.

That’s exactly what a database index does.

4. How Databases Search Without an Index

Without an index:

SELECT *
FROM users
WHERE email = '[email protected]';

Enter fullscreen mode Exit fullscreen mode

The database performs:

Full Table Scan

User 1
User 2
User 3
User 4
...
User 10,000,000

Enter fullscreen mode Exit fullscreen mode

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);

Enter fullscreen mode Exit fullscreen mode

The database now maintains a searchable structure.

Instead of:

Scan everything

Enter fullscreen mode Exit fullscreen mode

it can:

Jump directly to matching rows

Enter fullscreen mode Exit fullscreen mode

Result:

Milliseconds instead of seconds

Enter fullscreen mode Exit fullscreen mode

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

Enter fullscreen mode Exit fullscreen mode

Instead of searching every record:

1
2
3
4
...

Enter fullscreen mode Exit fullscreen mode

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
);

Enter fullscreen mode Exit fullscreen mode

Create an index:

CREATE INDEX idx_users_email
ON users(email);

Enter fullscreen mode Exit fullscreen mode

Now email lookups become dramatically faster.

8. The Performance Difference

Imagine:

10 million users

Enter fullscreen mode Exit fullscreen mode

Without index:

2–10 seconds

Enter fullscreen mode Exit fullscreen mode

With index:

1–20 milliseconds

Enter fullscreen mode Exit fullscreen mode

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);

Enter fullscreen mode Exit fullscreen mode

Optimized for:

WHERE email = ?

Enter fullscreen mode Exit fullscreen mode

Composite Index

CREATE INDEX idx_users_country_status
ON users(country, status);

Enter fullscreen mode Exit fullscreen mode

Optimized for:

WHERE country = ?
AND status = ?

Enter fullscreen mode Exit fullscreen mode

10. The Leftmost Prefix Rule

One of the most important concepts.

Given:

(country, status)

Enter fullscreen mode Exit fullscreen mode

The index can help with:

WHERE country = 'US'

Enter fullscreen mode Exit fullscreen mode

or:

WHERE country = 'US'
AND status = 'ACTIVE'

Enter fullscreen mode Exit fullscreen mode

But often not:

WHERE status = 'ACTIVE'

Enter fullscreen mode Exit fullscreen mode

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

Enter fullscreen mode Exit fullscreen mode

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

Enter fullscreen mode Exit fullscreen mode

doesn’t need fancy indexing.

Mistake #2

Too many indexes.

Every write becomes slower.

Mistake #3

Wrong column order.

(country, status)

Enter fullscreen mode Exit fullscreen mode

is not the same as:

(status, country)

Enter fullscreen mode Exit fullscreen mode

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 = '[email protected]';

Enter fullscreen mode Exit fullscreen mode

You’ll see something like:

Index Scan

Enter fullscreen mode Exit fullscreen mode

or:

Seq Scan

Enter fullscreen mode Exit fullscreen mode

If you see:

Seq Scan

Enter fullscreen mode Exit fullscreen mode

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 = ?

Enter fullscreen mode Exit fullscreen mode

Index email.

E-Commerce

WHERE product_id = ?

Enter fullscreen mode Exit fullscreen mode

Index product IDs.

Social Media

WHERE user_id = ?
ORDER BY created_at DESC

Enter fullscreen mode Exit fullscreen mode

Use a composite index:

(user_id, created_at)

Enter fullscreen mode Exit fullscreen mode

Banking

WHERE account_number = ?

Enter fullscreen mode Exit fullscreen mode

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

Enter fullscreen mode Exit fullscreen mode

into:

10 milliseconds

Enter fullscreen mode Exit fullscreen mode

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?”