A Practical Guide to Database Indexing (No Fluff)

Last month I spent an entire Saturday tracking down why a dashboard query had gone from 200ms to 40 seconds. The answer was, as it almost always is: a missing index. Here's what I actually needed to know, minus the theory-heavy stuff most guides open with.

An index is just a shortcut

Think of a database table as an unsorted phone book. Without an index, finding "everyone named Rivera" means reading every single page. An index on the last_name column is like a tab system — the database can jump straight to the right section instead of scanning everything.

When you actually need one

Add an index when a column shows up often in WHERE, JOIN, or ORDER BY clauses on a table with meaningful row counts — a few thousand rows and up is where the difference becomes noticeable.

CREATE INDEX idx_orders_user_id ON orders (user_id);

-- now this is fast, not a full table scan
SELECT * FROM orders WHERE user_id = 42;

The tradeoff nobody mentions enough

Indexes aren't free. Every index you add slows down writes, because the database has to update that index on every insert, update, and delete. A table with ten indexes on it will feel noticeably slower to write to than one with two. Index what you query often; don't index every column defensively.

Composite indexes and column order

If you filter on two columns together, a composite index usually beats two separate indexes. But column order matters — an index on (user_id, created_at) speeds up queries filtering by user_id alone or by both columns together, but does almost nothing for queries filtering by created_at alone.

CREATE INDEX idx_orders_user_created ON orders (user_id, created_at);

-- fast: uses the index
SELECT * FROM orders WHERE user_id = 42 AND created_at > '2026-01-01';

-- slow: created_at alone can't use this index efficiently
SELECT * FROM orders WHERE created_at > '2026-01-01';

How I actually found my slow query

I ran EXPLAIN ANALYZE on the offending query, which showed a sequential scan across 4 million rows instead of an index scan. That one line told me everything — a missing index on a foreign key column that was central to a join. Adding it took the query from 40 seconds back down to under 100ms.

The takeaway: don't guess. Run EXPLAIN ANALYZE before you touch anything. It will tell you exactly where the time is going, and usually the fix is smaller than you'd expect.