I had an admin dashboard that took under a second to load for months. Then one afternoon someone refreshed it and it just sat there spinning. Ten seconds. Fifteen. I assumed the server had fallen over.
It hadn’t. The orders table had quietly grown to two million rows, and one query, filtering by customer email, had never had an index on that column. It had been doing a full table scan the whole time. Nobody noticed at a thousand rows. Everybody noticed at two million.
I added one line, CREATE INDEX idx_orders_email ON orders(email);, and the query dropped from fifteen seconds to eleven milliseconds. Same query. Same data. One index.
What the database was actually doing before
Without an index, when you ask a database to find rows matching some condition, it has exactly one option: read every row in the table, check if it matches, and keep the ones that do. This is called a sequential scan, or a table scan.
For a hundred rows that’s instant. For two million rows, the database is reading two million rows off disk (or, if you’re lucky, from cache) just to answer one query about a single customer. It has no way of skipping ahead. It doesn’t know where the matching rows live, so it has to look at all of them.
That’s the whole problem. An index exists to give the database a shortcut so it doesn’t have to do that.
The index is basically a sorted lookup table
Most databases implement indexes using a structure called a B-tree, and honestly the mental model is close to the index at the back of a textbook. Instead of reading every page to find mentions of “transactions,” you flip to the index, find “transactions” listed alphabetically, and it tells you exactly which pages to open.
A B-tree index on a column keeps the values from that column sorted, along with a pointer to where the actual row lives on disk. When you search for email = 'bob@example.com', the database can binary-search the sorted structure, land on the right spot in a handful of steps, and jump straight to the matching rows. No scanning required.
This is also why an index helps with range queries (WHERE created_at > '2026-01-01') and sorting (ORDER BY created_at), not just exact matches. Sorted data makes ranges and ordering cheap too.
Creating and checking one in PostgreSQL
Creating an index is the easy part.
CREATE INDEX idx_orders_email ON orders(email);The part people skip is checking whether the database actually uses it. EXPLAIN ANALYZE shows you the real query plan, including whether it did a sequential scan or an index scan.
Before the index:
EXPLAIN ANALYZE SELECT * FROM orders WHERE email = 'bob@example.com';
-- Seq Scan on orders (cost=0.00..48541.00 rows=1 width=120)
-- (actual time=12.401..14203.887 rows=1 loops=1)
-- Filter: (email = 'bob@example.com'::text)
-- Rows Removed by Filter: 1999999
-- Planning Time: 0.112 ms
-- Execution Time: 14204.019 msAfter it:
EXPLAIN ANALYZE SELECT * FROM orders WHERE email = 'bob@example.com';
-- Index Scan using idx_orders_email on orders
-- (cost=0.42..8.44 rows=1 width=120)
-- (actual time=0.031..0.033 rows=1 loops=1)
-- Index Cond: (email = 'bob@example.com'::text)
-- Planning Time: 0.098 ms
-- Execution Time: 0.052 ms“Rows Removed by Filter: 1999999” is the line that tells the real story. That’s Postgres reading two million rows to throw away all but one of them, on every single query, until the index existed.
The same thing in MySQL
MySQL’s EXPLAIN doesn’t run the query the way EXPLAIN ANALYZE does by default, but it still tells you what the optimizer plans to do.
EXPLAIN SELECT * FROM orders WHERE email = 'bob@example.com';
-- +----+-------------+--------+------+---------------+------+---------+
-- | id | select_type | table | type | possible_keys | key | rows |
-- +----+-------------+--------+------+---------------+------+---------+
-- | 1 | SIMPLE | orders | ALL | NULL | NULL | 1998432 |
-- +----+-------------+--------+------+---------------+------+---------+type: ALL means a full table scan, and rows: 1998432 is MySQL’s estimate of how many rows it has to check. That’s your red flag. Add the index the same way as Postgres, MySQL’s CREATE INDEX syntax is identical:
CREATE INDEX idx_orders_email ON orders(email);Run EXPLAIN again and type changes to ref, with rows dropping to something close to 1.
-- +----+-------------+--------+------+------------------+------------------+------+
-- | id | select_type | table | type | possible_keys | key | rows |
-- +----+-------------+--------+------+------------------+------------------+------+
-- | 1 | SIMPLE | orders | ref | idx_orders_email | idx_orders_email | 1 |
-- +----+-------------+--------+------+------------------+------------------+------+You can also list every index on a table with SHOW INDEX FROM orders; if you’ve lost track of what’s already there, which, on a table that’s been through a few migrations, happens more often than I’d like to admit.
Composite indexes, and why column order matters
If you regularly filter on two columns together, like status and created_at, a single composite index usually beats two separate single-column indexes.
CREATE INDEX idx_orders_status_created ON orders(status, created_at);Here’s the part that trips people up. A composite index is sorted by the first column, then the second within each value of the first. Think of a phone book sorted by last name, then first name. You can jump straight to “Smith,” and within Smith you can jump to “John.” But you cannot efficiently find everyone named “John” across every last name using that same book.
So idx_orders_status_created speeds up WHERE status = 'shipped' and WHERE status = 'shipped' AND created_at > '2026-01-01'. It does basically nothing for WHERE created_at > '2026-01-01' on its own, because the index isn’t sorted by date first. If your queries filter on created_at alone fairly often too, you probably want a separate index for that, or to put it first in the composite one, depending on which query runs more.
The cost nobody mentions when they tell you to “just add an index”
For a while I had a habit of indexing pretty much every column that showed up in a WHERE clause anywhere in the codebase. That’s not free.
Every index has to be updated on every INSERT, UPDATE, or DELETE that touches the indexed column. Ten indexes on a table means every write updates eleven data structures instead of one. On a table that’s read constantly but written rarely, that trade is obviously worth it. On a table with heavy write traffic and indexes nobody’s queries actually use, you’re just slowing down every write for no benefit.
Indexes also take up disk space, sometimes a lot of it. A B-tree index on a large text column can end up nearly as large as the table itself.
The rule I follow now: index columns that show up in WHERE, JOIN, or ORDER BY clauses on tables that get queried a lot, and periodically check for indexes that never get used. Postgres makes this easy:
SELECT indexrelname, idx_scan
FROM pg_stat_user_indexes
WHERE idx_scan = 0;Anything showing zero scans since the last stats reset is a candidate to drop, assuming it’s been running long enough to be a fair sample.
Cases where an index quietly does nothing
A few situations where people expect an index to help and it doesn’t.
Low-cardinality columns. An index on a boolean column, or a status column with three possible values spread evenly across the table, usually doesn’t help much. If half the table matches your WHERE clause, the database is often better off just scanning the table than hopping through an index and then fetching each matching row individually.
Leading wildcard searches. WHERE email LIKE '%@gmail.com' can’t use a standard B-tree index, because the sorted order only helps when you know how the value starts, not how it ends. WHERE email LIKE 'bob%' can use it fine.
Functions wrapped around the indexed column. WHERE LOWER(email) = 'bob@example.com' won’t use a plain index on email, because the index stores the raw values, not their lowercased form. Postgres solves this with expression indexes (CREATE INDEX ON orders(LOWER(email))), MySQL added functional indexes in 8.0.13 with the same idea. Without one of those, you’re back to a full scan even though email is indexed.
What I’d actually tell someone starting out
Add an index when a specific, real query is slow, not preemptively on every column that looks important. Confirm it worked with EXPLAIN ANALYZE in Postgres or EXPLAIN in MySQL, don’t just assume. And if a table’s write volume is high, think about whether each index is earning its keep before you add the next one.
That dashboard loads instantly now. Nobody’s opened EXPLAIN on it since. Which is, honestly, the whole point of doing it right the first time.
References
- PostgreSQL Indexes documentation — index types, multicolumn indexes, and when the planner will or won’t use one
- MySQL 8.4 Optimization and Indexes — how InnoDB uses indexes, including functional indexes
- Use the Index, Luke — a deep, database-agnostic explainer on how indexes actually work internally