The Backend Developer’s Guide to Indexes

At some point, every backend engineer faces the same crisis: A query that used to take 10ms now takes 5 seconds. The database is sweating, the users are complaining, and the senior engineer on your team says two words: "Add an index."

You add it. Suddenly, everything is fast again. It feels like magic.

But in the backend, magic is just an abstraction we don’t understand yet. If you treat indexes as magic "go-fast" buttons, you’ll eventually break your database in ways that are much harder to fix.

Let’s start with a comforting lie. “Indexes make queries faster.”

That’s true. It’s also incomplete.

Indexes don’t magically speed things up. They change how the database thinks about data. And once you understand that, indexes stop feeling like a trick and start feeling like a trade.

Because every index you add helps one thing and quietly hurts another.

The Haystack Problem (Full Table Scans)

Imagine I give you a box containing 1,000,000 unsorted index cards, each with a name and a phone number. I ask you to find "John Smith"

You ask:

SELECT * FROM users WHERE name = 'batman';

Without an index, the database has no choice.

It starts at the beginning.
It checks the first row.
Then the second.
Then the third.

This is not stupidity. It’s honesty.

You have no choice: you have to look at every single card until you find him. In database terms, This is called a full table scan, and it’s the default behavior of every database on earth. It’s fine for 10 cards. It’s a disaster for 10 million.

An Index is a separate, highly organized list that points to the original data.

What an Index Actually Is

The secret to an index isn't just "organization"—it’s Sorting.

If I sort those 1,000,000 cards alphabetically, you don't have to look at all of them. You go to the middle, see "M," and realize "J" must be in the first half. You’ve just turned a million-step problem into a 20-step problem.

An index is a sorted copy of a specific column. When you index a user_id, the database creates a separate structure where those IDs are perfectly ordered.

How It Works: The B-Tree

Most databases use a structure called a B-Tree for indexing. You don't need to be a math genius to understand it; just think of it as a "Decision Tree."

1. The database looks at the top (Root).

2. It asks: "Is the ID I'm looking for greater than 500?"

3. It follows the "Yes" or "No" path down to the next level.

4. In 3 or 4 hops, it finds exactly where the data lives on the disk.

The Hidden Cost: The "Write Penalty"

If indexes make things fast, why don't we index every single column?

Because indexes aren't free. Every time you INSERT, UPDATE, or DELETE a row, the database has to:

1. Write the data to the main table.

2. Find the correct spot in every single index associated with that table.

3. Re-sort and re-balance those structures.

An index speeds up Reads but slows down Writes. If you have a table that gets 10,000 writes a second but only 1 read an hour, adding five indexes will actually hurt your performance.

When Indexes Don’t Work (And Make Things Worse)

Not every column deserves an index. Here are the "Red Flags":

• Low Cardinality: This is a fancy way of saying "not many unique values." If you index a gender column or a boolean (True/False), the index doesn't help. The database still has to look at 50% of the table. It’s often faster to just do a table scan.

• Small Tables: If your table has 50 rows, an index is actually slower. It takes more work to load the index into memory than it does to just read the 50 rows.

• Frequent Changes: If a column value changes every few seconds, the database spends all its time "re-indexing" instead of serving users.

The "Golden Rule" of Indexing

Don't index based on what you have; index based on how you query.

If you never search for users by their "Date of Birth," an index on dob is just wasted disk space and slower writes. A engineer looks at the WHERE, JOIN, and ORDER BY clauses in their code—that is where the indexes belong.

Indexes are opinionated. They are built around specific access patterns. An index on (email) helps:

WHERE email = ?

It does nothing for:

WHERE LOWER(email) = ?

Or:

WHERE email LIKE '%@gmail.com'

Indexes reward predictable thinking.
They punish creativity.

This is why changing queries can silently kill performance.

Composite Indexes: Order Is Not Decoration

Consider:

INDEX (user_id, created_at)

This is not the same as:

INDEX (created_at, user_id)

Why?

Because indexes are sorted. The database can efficiently answer:

WHERE user_id = ? AND created_at > ?

But struggles with:

WHERE created_at > ? AND user_id = ?

The order defines the thinking path. Indexes don’t adapt. They obey structure.

Indexes and “Where” Revisited

Remember the previous article. “Where” matters. Indexes decide where the database should look. But in distributed systems, that becomes more complex:

Which shard has the index?
Which replica is allowed to use it?
Is the index local or global?

A perfect index on the wrong node is still slow.

Right Way of Think About Indexes

Not “What index do I need?” But:

What queries must be fast?
Which ones can be slow?
What can I afford on writes?
What patterns are stable?

Indexes are commitments. Not optimizations.

What Comes Next

In the next one, we’ll talk about Caching: Why Moving Data Closer is the Ultimate Optimization.

This article is part of the Thinking in Backend series, where we learn backend engineering by understanding how systems think, not just how databases execute.