I Finally Understood Why Database Indexes Make Queries Faster

I used to think adding an index to a database was basically a magic “make this query faster” button.

Then I understood what was actually happening underneath.

Imagine a users table with 1 million records.

If we run:

SELECT * FROM users
WHERE email = '[e-mail]';

Without an index, the database may have to check rows one by one until it finds the match.

That's basically:

“Let me search through everything.” 😭

Now add an index:

CREATE INDEX idx_users_email
ON users(email);

The database can use a specialized data structure to locate the matching value much more efficiently.

It's similar to the difference between:

📚 Searching every page of a book
vs.
🔖 Using the index at the back of the book

But here's the part I didn't realize:

Indexes aren't free.

Every index takes storage, and when you INSERT, UPDATE, or DELETE data, the database may also need to update the indexes.

So blindly adding indexes isn't the solution.

A better approach is:

1. Find queries that are actually slow.
2. Check how the database executes them.
3. Add indexes to columns frequently used for filtering, joining, or sorting.
4. Measure the improvement.
5. Remove indexes that aren't providing value.

For example, if your application constantly does:

SELECT * FROM users
WHERE email = ?;

then indexing email makes a lot of sense.

But creating indexes on every column?

Probably not.

The lesson I took away

Database performance isn't just about writing faster SQL.

It's about understanding how the database finds your data.

And honestly, learning this changed the way I think about backend development.

Don't optimize blindly. Understand what the database is doing first.