Adding an index and adding the right index are two different skills, but most advice treats them as one and the same. “Just add an index” gets repeated so often as a fix that it’s easy to absorb the phrase without absorbing the mechanism behind it — which is exactly the position I was in for a long stretch, passing the advice along because every performance guide agreed on it, not because I could explain why.
That changed the day I added an index to a write-heavy table and watched insert performance get noticeably worse instead of better. Something had to give, and what gave was my assumption that indexes were a one-directional improvement. That’s when I sat down and actually worked out what an index does, rather than just knowing that it’s supposed to help.
The Core Mental Model: A Sorted Structure, Not Magic
Without an index, the database answers a filtered query by checking every row, one by one — the equivalent of hunting for a single fact in a book that has no index, where your only option is reading from the first page until you stumble onto the right one.
An index changes that by existing as a separate, sorted structure — commonly a B-tree — that maps column values directly to where the corresponding rows physically live. Instead of reading through everything, the database can navigate almost straight to the rows that match, in the same way a book’s index lets you flip to the correct page instead of reading cover to cover.
A Concrete Example of Why This Matters at Scale
Picture a users table holding one million rows, with a query filtering for one specific email address. With no index on that column, the worst case means checking all one million rows to find the match. With an index in place, the database can locate it in roughly twenty comparisons, because each step through a sorted structure like this one cuts the remaining search space roughly in half. That gap — a million rows versus about twenty — is the entire reason a single indexing decision can turn a query that takes several seconds into one that returns instantly.
What Gets Indexed and How to Create One
A basic index gets created like this: CREATE INDEX idx_users_email ON users(email). That statement builds a sorted structure specifically for the email column on the users table.
Indexes aren’t limited to a single column — you can build one across several, known as a composite index, and the order those columns appear in matters more than it might seem. An index on (customer_id, order_date) handles queries filtering by customer_id alone, or by customer_id and order_date together, quite well. It won’t help a query that filters by order_date alone, though, because the structure is sorted first by customer_id, with dates only sorted within each customer’s own grouping.
Why More Indexes Isn’t Automatically Better
This is the part that stayed hidden from me for longer than it should have. Every index has to be updated any time the underlying data changes — an insert, an update, a delete — because the sorted structure can’t be allowed to drift out of accuracy. That means every additional index brings real write overhead and extra storage with it, even on rows and columns that particular index doesn’t touch. A table that takes frequent writes, and that’s accumulated a pile of indexes nobody ever revisited, can end up with noticeably slower inserts and updates purely from that upkeep cost — which is precisely the situation that pushed me to learn this mechanism properly in the first place.
When an Index Won’t Help, Even Though It Exists
Applying a function to the indexed column inside your WHERE clause — filtering on LOWER(email) instead of email directly, say — typically can’t take advantage of a standard index built on the raw values, since the index is sorted according to those raw values, not whatever the function would transform them into.
Using a leading wildcard in a LIKE pattern — searching for something ending in a given string, like '%gmail.com' — usually can’t use a standard index efficiently either, since there’s no clear starting point for the structure to jump to when a match could begin anywhere in the value. A trailing wildcard, by contrast, like 'john%', plays nicely with the index, since the structure can jump straight to where values starting with “john” begin.
Indexing a column with very low selectivity — a boolean column where nearly every row shares the same value, for instance — tends to offer little real payoff, because the index doesn’t narrow the search space in any meaningful way. In that situation, the query optimizer will often bypass the index entirely and run a full scan instead, and it’s right to do so — that really is the faster option when most rows match anyway.
How to Check Whether Your Index Is Being Used
Rather than assuming an index is doing its job simply because it exists, run EXPLAIN (or EXPLAIN ANALYZE, depending on your database) to see the execution plan your database chose for a given query. It tells you directly whether an index scan was used or whether the engine fell back to scanning the whole table. That’s the reliable way to confirm an index is helping a specific query — not inferring it from the fact that the index is sitting there.
A Worked Example
Say you’re finding every order a specific customer placed in the last thirty days. Without an index on customer_id, that means scanning the whole orders table. Add an index on customer_id, and the optimizer can jump straight to that customer’s rows. If queries like this one routinely filter by both customer_id and order_date together, a single composite index on (customer_id, order_date) handles that combined pattern more efficiently than keeping two separate single-column indexes around would.
When to Reach for an Index, and When to Leave It Alone
If a column shows up often in WHERE clauses, JOIN conditions, or ORDER BY clauses, on a table with a meaningful row count, and that column has decent selectivity — most values distinct rather than clustered into a handful of repeats — an index is very likely worth whatever write overhead it costs. If the table is small, rarely queried, or the column has poor selectivity, an index is more likely to sit there adding upkeep cost without making your actual queries any faster.
The Investment This Concept Deserves
Once my mental model shifted from “add an index and things speed up” to “a sorted structure that lets the database jump to matching rows instead of scanning everything,” I could reason through specific cases on their own terms — the low-selectivity column, the leading-wildcard search, the write-heavy table — instead of treating index creation as a universal best practice to sprinkle everywhere without checking whether it fit the situation.
Are you trying to decide whether a specific slow query or a specific table would benefit from an index? Describe the query and the table’s rough size and write frequency, and I can help you work through whether an index is the right fix here.