Database Indexing Explained

Table of Contents +

Picture this. You’re building an app and your users table has grown to ten million rows. Then you run a simple query:

You ask the database for one user, something like SELECT * FROM users WHERE email = 'alex@mail.com'.
When the table was small, this came back instantly.
Now the same query takes a few seconds, and your app feels slow.

So what changed? Nothing about the query. What changed is how much work the database has to do to find that one row. And the fix for this slowness is a thing called an index. Let’s see why the query got slow, and how an index makes it fast again.

🎯 The Problem

Here’s what your database is actually doing when there’s no index:

You asked for the row where email = 'alex@mail.com'. But the database has no quick way to find it.
So it does the only thing it can. It starts at the first row and checks them one by one, all the way down, until it finds a match.
This row-by-row checking of the whole table is called a full table scan.
With ten rows that’s nothing. With ten million rows, it’s reading ten million rows just to find one. That’s the slowness you felt.

And it gets worse as the table grows:

The bigger the table, the longer every scan takes. Twice the rows, roughly twice the time.
These scans also burn disk reads and memory, so they slow down everything else on the server too.
So a query that was fine on day one becomes a real problem once real users show up.

The core problem in one line

Without an index, finding a row means looking at every row. That’s fine for tiny tables, but it falls apart as your data grows.

📖 Real-World Analogy

Think about a big textbook, maybe a thousand pages. You want everything about the word “photosynthesis”. You’ve got two ways to find it:

The slow way. You open page one and read every single page until you spot the word. On a thousand pages, that takes forever.
The fast way. You flip to the index at the back of the book. It’s an alphabetical list that says “photosynthesis … page 412”. You jump straight to 412. Done.

A database index works exactly like that book index:

The slow way is the full table scan, reading every row.
The book’s index is the database index, a separate sorted list that points you to the right spot.
And just like the back of a book, the index doesn’t hold the whole chapter. It just holds the keyword and a pointer to where the real content lives.

A phone book is the same idea, right? Names are sorted alphabetically, so you don’t read every name to find “Alex”. You jump close and narrow down fast. Keep this picture in your head. Everything below maps back to it.

🗂️ What is a Database Index

So now we can define it plainly:

A database index is a separate data structure that stores the values of one or more columns in sorted order, along with a pointer to the actual row.
It’s built for one job: helping the database find rows fast, without scanning the whole table.
You create one on the columns you search a lot. For example, CREATE INDEX idx_email ON users(email) builds an index on the email column.
After that, a query like WHERE email = 'alex@mail.com' can use the index to jump straight to the row, instead of scanning all ten million.

One thing worth knowing up front:

Your table’s primary key (like the id column) is indexed automatically. The database does this for you, because you look rows up by id all the time.
So you’ve actually been using indexes already without creating any. The new ones you add are for your other important columns.

⚙️ How an Index Works

Okay so an index is “sorted with pointers”. But how does sorted help so much? The secret is the structure underneath. Most databases store indexes as a B-tree (or a close cousin, the B+ tree).

Let’s not get lost in the name. Here’s the idea of a B-tree, plain and simple:

It’s a tree of sorted values, organized so the database can throw away most of the data at every step.
You start at the top node. It tells you which branch to follow, left or right, based on the value you’re looking for.
Each branch narrows the range down further, like guessing a number between 1 and 100 by always cutting the range in half.
After just a few hops, you land on the exact value, and it points you to the row on disk.

Here’s the same thing as a picture. Say we’re looking for id = 7.

The big win is in how the numbers grow:

A full table scan on a million rows checks up to a million rows. That’s a lot.
A B-tree lookup on the same million rows takes only around 20 hops to land on the answer.
That’s because each hop cuts the remaining data down by a big chunk, instead of crossing off one row at a time.
This “cut it down fast” behavior is why indexed lookups stay quick even as the table gets huge.

Here’s the difference side by side.

Sorted is the whole trick

The reason an index is fast is that its data is kept in sorted order. Sorted data lets you skip huge chunks at once, the same way you don’t read every name in a phone book. An unsorted pile would force you to check everything, which is exactly the scan we’re trying to avoid.

⚡ Benefits

So what do you actually get out of an index? The wins are all about reading data faster:

Fast lookups. Finding a row by an indexed column goes from “scan everything” to “a few quick hops”. This is the headline benefit.
Faster WHERE filters. Queries that filter on an indexed column, like WHERE email = ..., get to skip the full scan.
Faster JOINs. When you join two tables on a column, an index on that column lets the database match rows quickly instead of scanning.
Faster ORDER BY and sorting. Since the index is already sorted, the database can often hand back sorted results without doing the sort work again.
Steady speed as you grow. This is the real magic. An indexed lookup stays fast whether you have a thousand rows or ten million.

In short, indexes make your reads quick and keep them quick. For most apps, where you read data far more often than you write it, that’s a huge deal.

⚠️ The Trade-offs

Now here’s the catch, and this is the part beginners miss. Indexes are not free. You’re trading something to get that read speed:

Slower writes. Every time you INSERT, UPDATE, or DELETE a row, the database has to update the indexes too, so they stay sorted and correct. More indexes mean more updating on every write.
Extra storage. An index is a separate structure sitting on disk. It takes up real space, on top of the table itself. Index a lot of columns and that space adds up.
Maintenance cost. Indexes have to be kept in order as data changes. That’s ongoing work the database does in the background.

So an index speeds up reads but slows down writes and eats storage. Here’s the trade-off laid out.

What you do	Without an index	With an index
Read / search a row	Slow (full table scan)	Fast (a few hops)
Insert / update / delete	Faster (nothing extra to update)	Slower (index must update too)
Storage used	Just the table	Table plus the index

Don't index everything

It’s tempting to slap an index on every column “just in case”. Don’t. Each extra index makes your writes slower and uses more disk. The goal is to index the columns you actually search on, and leave the rest alone.

🧩 When to Add an Index

So how do you decide? The simple rule is: index the columns the database searches on a lot. In practice, look for these:

Columns in WHERE clauses. If you keep filtering by email or user_id, those are prime candidates.
Columns used in JOINs. The columns you join two tables on benefit a lot from an index.
Columns in ORDER BY. If you often sort by a column like created_at, an index on it speeds that up.
High-read, low-write columns. Columns you read constantly but rarely change are the sweet spot, since you get the read win without paying much write cost.

And just as important, know when to skip it:

Tiny tables. If a table has a few hundred rows, a full scan is already fast. An index buys you nothing.
Columns you rarely search by. No point indexing a column no query filters on.
Write-heavy columns that change constantly. Here the slower-write cost can outweigh the read benefit.

A handy starting point

Look at your slowest queries first. See which columns show up in their WHERE, JOIN, and ORDER BY parts. Those are usually exactly the columns worth indexing. Fix the painful queries, don’t index blindly.

⚠️ Common Mistakes and Misconceptions

A few things trip people up with indexes. Let’s clear them out:

“Just index every column to be safe.” No. Every index slows down writes and uses storage. Indexing everything can make your database slower overall, not faster.
“Indexes are free.” They’re not. You pay in write speed and disk space. The read speedup is a trade, not a freebie.
“More indexes always mean faster queries.” Not true. A query usually uses one index. Piling on extra ones it never uses just adds write cost for no read benefit.
“An index changes my data.” It doesn’t. An index is a separate helper structure. Your table and its rows stay exactly the same.
“The database always uses my index.” Not guaranteed. The query planner decides. If a query asks for most of the table anyway, the database may skip the index and just scan, because that’s actually faster.

🛠️ Design Challenge

Try this on your own to test yourself.

Imagine an online store with an orders table holding fifty million rows. The app runs these queries a lot:

Look up all orders for one customer: WHERE customer_id = ...
Show a customer’s orders newest first: ORDER BY created_at
Find one order by its id: WHERE id = ...

Now answer these:

Which columns would you put an index on, and why?
Which query already has an index without you adding one?
The orders table also gets thousands of new inserts every minute. How does that change your thinking about how many indexes to add?

Work through it like you would in an interview. The goal is to balance fast reads against the write cost, not to index everything.

🧩 What You’ve Learned

You can now explain why a query gets slow and how an index fixes it. Here’s what you’ve picked up.

✅ Without an index, the database does a full table scan, checking every row to find a match.
✅ A database index is a separate sorted structure with pointers to rows, like the index at the back of a book.
✅ Indexes are usually B-trees, which stay sorted so lookups take just a few hops, even on huge tables.
✅ Indexes speed up reads: lookups, WHERE filters, JOINs, and ORDER BY.
✅ The trade-off is slower writes and extra storage, so you don’t index everything.
✅ Index the columns you filter, join, and sort on, and remember the primary key is already indexed for you.

Check Your Knowledge

Test what you learned. Pick an answer for each question, then click Check.

🚀 What’s Next?

You now understand how to make queries fast inside a database. Next, zoom out to how databases fit into the bigger system.

SQL vs NoSQL breaks down the two big families of databases and when to pick each one.
Introduction to Caching shows how to make reads even faster by keeping hot data close, the next layer of speed on top of indexing.

Get these two down and you’ll have a solid grip on the data layer that every backend and system design interview leans on.

Previous Column-Oriented Databases Explained Next Database Normalization Explained

Share & Connect

Share on LinkedIn