There is a rite of passage every backend developer goes through: a query that was instant on your laptop grinds to a halt in production once the table has a few million rows. Nine times out of ten, the cure is database indexing — and understanding how indexes work is the difference between an app that scales and one that falls over.
The library analogy that finally makes it click
Imagine a library with no catalog. To find a book by title, you would walk every shelf, checking each spine — a “full table scan.” An index is the catalog: a sorted structure that lets the database jump almost straight to the rows you want instead of reading every one. That is the entire idea. Everything else is detail.
Creating an index
If you frequently look users up by email, index that column:
CREATE INDEX idx_users_email ON users(email);Now a query like SELECT * FROM users WHERE email = 'x@y.com' uses the index to find the row in a handful of steps instead of scanning the whole table. On a large table, that can turn a multi-second query into a sub-millisecond one.
Why not index everything?
Because indexes are not free. Every index you add has to be updated on every insert, update, and delete — so over-indexing quietly taxes all your writes. Indexes also take disk space. The craft is indexing the columns you actually filter, join, and sort on, and no more. An index nobody’s queries use is pure overhead.
Composite indexes and column order
You can index multiple columns together, and the order matters more than people expect:
CREATE INDEX idx_orders_cust_date
ON orders(customer_id, created_at);This index helps queries that filter by customer_id alone, or by customer_id and created_at — but not queries that filter by created_at alone. Think of it like sorting by last name, then first name: the ordering is only useful from the left. Put the most selective, most-filtered column first.
Let the database tell you
Stop guessing and use EXPLAIN (or EXPLAIN ANALYZE). Prefix your slow query with it and the database shows its plan — whether it used an index or fell back to a full scan. That output is the single most useful tool for diagnosing slow queries, and learning to read it will teach you more about database indexing than any article can.
The ways queries silently refuse your index
Creating an index doesn’t guarantee it gets used, and the failure modes are sneaky because the query still works — just slowly. The most common index-killers:
-- Function on the indexed column: index skipped
WHERE LOWER(email) = 'x@y.com'
-- Leading wildcard: can't seek into a sorted structure
WHERE name LIKE '%smith'
-- Type mismatch: comparing a string column to a number
WHERE phone = 5551234
-- OR across different columns: often falls back to a scan
WHERE email = 'x@y.com' OR username = 'xy'Each has a fix. For case-insensitive lookups, either store a normalized copy or — in PostgreSQL — create a functional index on LOWER(email) so the transformed value itself is indexed. Leading-wildcard searches usually mean you’ve outgrown LIKE and want full-text search. Type mismatches are a one-character fix (quote it). And the OR case often runs dramatically faster rewritten as two indexed queries combined with UNION. The meta-lesson: an index is a sorted structure, and anything that prevents the database from seeking into that sorted order sends it back to scanning.
Covering indexes: when the index answers the whole query
Normally an index lookup is a two-step dance — find the matching entries in the index, then hop to the table to fetch the rest of each row. But if every column the query needs is in the index itself, the table visit disappears entirely:
-- This query...
SELECT customer_id, created_at FROM orders WHERE customer_id = 42;
-- ...is fully answered by this index, no table access at all
CREATE INDEX idx_orders_cust_date ON orders(customer_id, created_at);That’s a covering index, and in EXPLAIN output it shows up as the coveted “index-only scan” (or “Using index” in MySQL). For hot queries that run thousands of times a minute, deliberately widening an index to cover them — some databases even have an INCLUDE clause for exactly this — is one of the cheapest big wins in database tuning. Just remember the trade: wider indexes cost more on writes, so reserve the trick for queries that earn it.
Uniqueness and foreign keys: the indexes you get (and don’t)
Two housekeeping facts prevent common surprises. First, primary keys and UNIQUE constraints automatically create indexes — no need to add another on the same column, and a UNIQUE constraint is often the honest choice anyway since it documents intent and speeds lookups. Second, and this one bites people: in PostgreSQL, declaring a foreign key does not automatically index the referencing column. Every JOIN on that relationship and every cascading delete walks the table until you add the index yourself. (MySQL’s InnoDB does create it automatically, which is exactly why people migrating between the two get surprised.) Auditing your foreign-key columns for missing indexes is a twenty-minute exercise that has rescued many a sluggish application.
Frequently asked questions
How do I find which indexes I’m missing? Turn on your database’s slow-query log, take the worst offenders, and run each through EXPLAIN ANALYZE looking for sequential scans on large tables. Postgres’s pg_stat_user_tables view even counts scans per table — a table with millions of sequential scans is begging for an index.
Can indexes hurt read performance too? Essentially no — unused indexes tax writes and disk, not reads. The optimizer simply ignores irrelevant indexes. The realistic read-side risk is the optimizer occasionally choosing a poor index; that’s rare and usually signals stale table statistics (run ANALYZE).
Should I index low-cardinality columns like status or boolean flags? Usually not alone — an index on a column with three distinct values barely narrows anything. But as the second column of a composite index ((customer_id, status)), or as a partial index (WHERE status = 'pending' — indexing only the rows you actually query), low-cardinality columns become genuinely useful.
The takeaway
Index the columns you search, join, and sort on; resist the urge to index everything; mind the column order in composite indexes; and let EXPLAIN guide you. Do that, and the “it was fast yesterday” performance cliff mostly stops happening.
