Database Indexes: What Are They and Why Should You Care? (Part 1)
I've been working with databases for a while now — writing queries, setting up tables, dealing with foreign keys — and I've always known that indexes make queries faster. That part was never a mystery to me. But what I didn't know was the other side of the story: when should you actually use them? When can they hurt you? How can something that speeds up your reads actually slow down your writes? Those were the questions I wanted to dig into, and here's what I found.
The Problem: Searching Without an Index
Imagine you have a users table with 1 million rows, and you run this:
SELECT * FROM users WHERE email = 'esteban@example.com';
Without an index, the database has to look at every single row in the table to find the match. This is called a full table scan. It's like flipping through every page of a book to find one sentence. With 1,000 rows, that's fine. With 1 million? That's a problem.
So What Is an Index, Really?
The easiest way to think about it: an index is like the index at the back of a book. Instead of reading the whole book to find where "B-tree" is mentioned, you go to the back, find "B-tree," and it tells you "page 42." You jump straight there.
A database index works the same way. It's a separate data structure that the database maintains alongside your table. It maps column values to the rows where those values live, so the database can jump directly to the right spot instead of scanning everything.
When you create an index:
CREATE INDEX idx_users_email ON users(email);
The database builds a sorted structure (more on that in a second) that lets it find esteban@example.com in a fraction of the time.
Under the Hood: B-Trees
Most database indexes use a data structure called a B-tree (balanced tree). I won't pretend I fully grasped this on the first read, but here's the simplified version that clicked for me.
Think of it like a tree structure where each node can have multiple children. The values are kept sorted, and the tree stays balanced — meaning every path from the root to a leaf is roughly the same length.
When the database searches for a value, it starts at the root and makes a series of comparisons to navigate down the tree. At each level, it narrows the search space dramatically. For a table with 1 million rows, a B-tree might only need 20 comparisons to find your row. Compare that to potentially 1 million comparisons with a full table scan.
This is why indexes make such a huge difference. It's the difference between O(n) and O(log n) for the folks who remember Big O notation.
The Part I Didn't Know: Indexes Can Hurt You
This was the big discovery for me. I always knew the positive side — indexes make reads faster. But I had no idea they could be negative. Turns out, indexes aren't free.
Every time you INSERT, UPDATE, or DELETE a row, the database has to update the index too. If you have 10 indexes on a table, that's 10 extra structures that need to be maintained on every single write. This means:
- Reads get faster — the part I already knew.
- Writes get slower — this was the surprise. Every write now has extra bookkeeping, and it can add up fast.
- Storage goes up — indexes take up disk space. Sometimes a lot of it.
So you can't just index everything and call it a day. It's a balancing act between read performance and write performance, and that's where the real skill comes in — knowing when and what to index.
A Quick Experiment That Made It Real
What really drove this home for me was running a simple test. I created a table with a million random rows and timed a query with and without an index:
-- Without index: ~450ms
SELECT * FROM users WHERE email = 'test_999999@example.com';
-- After creating the index: ~2ms
CREATE INDEX idx_users_email ON users(email);
SELECT * FROM users WHERE email = 'test_999999@example.com';
Going from 450ms to 2ms was wild. That's a 225x improvement from one line of SQL. It made the concept feel a lot less abstract.
What's Next
Now that I understand the basics of what indexes are and why they matter, the next question is: how do you use them well? Not every column needs an index, and sometimes the order of columns in an index matters a lot.
In Part 2, I'll dig into composite indexes (indexes on multiple columns), learn how to read EXPLAIN output to see if your queries are actually using your indexes, and talk about the common mistakes people make when indexing.
Stay tuned — this stuff gets even more interesting when you start applying it to real queries.