Database Indexes: Why Your Queries Are Slow

Your app was fast last month. Today, one page takes ten seconds to load. You didn't change the code — so what happened?

Here's the usual culprit: your table grew. A query that skimmed through 500 rows without breaking a sweat is now wading through 500,000, and the database is doing it the slow way — reading every single row to find the few you asked for. The fix is one of the highest-value tricks in all of databases: the index.

The phone book analogy

Imagine an old-fashioned phone book. Finding someone by last name is nearly instant — the book is sorted by last name, so you flip straight to the right section. Now try finding someone by first name. There's no shortcut. You have to read every page, start to finish, and hope you don't blink past them.

Databases face the exact same situation:

• Full table scan: the "read every page" approach. The database checks every row in the table against your condition. Fine for small tables, brutal for big ones.
• Index seek: the "sorted phone book" approach. The database jumps almost directly to the matching rows. This is the difference between scanning a million rows and jumping straight to one.

What an index really is

An index is not magic and it's not a setting — it's a real, physical thing: a separate, sorted structure that the database builds from one or more columns, with each entry pointing back to its full row. Sorted data can be searched incredibly fast (that's the phone book trick), so the database searches the small sorted index first, then follows the pointer to the actual row.

Remember the clinic system from our Conversation to System Design series? The Appointment table holds a PatientId foreign key. Every time we show a patient's appointment history, we run something like:

SELECT AppointmentDate, Status
FROM Appointment
WHERE PatientId = 42;

Without an index, that's a full scan of every appointment ever made. One line changes everything:

CREATE INDEX IX_Appointment_PatientId
    ON Appointment (PatientId);

Now the database keeps a sorted list of PatientId values and jumps straight to patient 42's entries. Foreign key columns like this one — the ones we created in part 6 of the series — are prime index candidates, because they're exactly the columns you filter and join on all day long.

When to add an index

Look at the columns your queries actually use:

• WHERE columns: anything you filter by, like WHERE PatientId = 42 or WHERE Age > 30.
• JOIN columns: the keys you connect tables with — almost always foreign keys.
• ORDER BY columns: sorting is free if the data is already sorted in an index.

If a slow query filters, joins, or sorts on a column that has no index, you've very likely found your fix.

The trade-offs nobody mentions

If indexes are so great, why not index every column? Because indexes aren't free:

• Slower writes: every INSERT, UPDATE, and DELETE must also update every index on that table. Each index is one more sorted structure to maintain.
• Storage: an index is a real copy of that column's data, sorted and stored. It takes disk space.
• Diminishing returns: the database only picks the most helpful index for a query anyway. A pile of unused indexes is pure cost with zero benefit.

The habit to build: index deliberately, based on the queries you actually run — not defensively, "just in case."

How do you know you need one?

You don't have to guess. Every serious database can show you its query plan — a report of exactly how it executed your query. If the plan says it did a table scan on a big table, and your query filters on an unindexed column, the evidence is right there.

That's the whole story: slow queries usually aren't mysterious, they're just unindexed. You now know more about this than a surprising number of working developers. For your next step, revisit how those foreign keys got there in foreign keys and junction tables, or zoom back out with SQL vs NoSQL: Which Should You Learn First?