Indexes: The Cheapest Performance Win You Keep Skipping

Of all the performance work I get called in for, database indexing has the best ratio in the business: an hour of reading query plans, one CREATE INDEX, and a 40-second report becomes 80 milliseconds. It's also the area with the widest gap between "I know indexes make queries fast" and actually being able to design one — because the useful knowledge isn't the syntax, it's the mental model of what the database does with your WHERE clause. Let's build that model.

The B-tree, and the one property everything follows from

A B-tree index is a sorted structure: walk from the root a few hops and you land at your value, then read leaf entries in order. Every practical rule descends from that sortedness:

• Equality and ranges are cheap (= , <, BETWEEN, LIKE 'abc%') — they're a seek plus a scan along the leaves.
• Anything that defeats sortedness defeats the index: LIKE '%abc' (sorted by prefix, not suffix), WHERE YEAR(created_at) = 2026 (the index stores created_at, not YEAR(created_at) — rewrite as a range, or in Postgres, index the expression), and implicit type casts (WHERE phone = 5551234 against a varchar column casts every row — a genuinely classic outage).
• ORDER BY can be free. If the index order matches the query's order, sorting costs nothing; if not, the database sorts in memory (or worse, on disk). ORDER BY created_at DESC LIMIT 20 on an indexed column reads exactly 20 leaf entries and stops.

Composite indexes: order is the whole game

The most consequential — and most-botched — decision. An index on (tenant_id, status, created_at) is sorted by tenant, then status within tenant, then date within that. Think of a phone book sorted by surname-then-firstname: instantly useful for "all Yılmaz", useless for "everyone named Berkan".

-- One index: (tenant_id, status, created_at)
WHERE tenant_id = 7 AND status = 'open' ORDER BY created_at DESC   -- ✅ perfect
WHERE tenant_id = 7                                                -- ✅ prefix works
WHERE status = 'open'                                              -- ❌ no leading column
WHERE tenant_id = 7 AND created_at > '2026-01-01'                  -- ⚠ uses (tenant_id),
                                                                   --   range-scans the rest

The design heuristic that serves in 90% of cases: equality columns first (most selective early), then the range or sort column last — because once the index hits a range, columns after it stop narrowing the search. And the corollary people miss: that composite already covers queries on tenant_id alone, so the separate single-column index next to it is dead weight — every index is a tax on writes and a candidate for deletion. (In multi-tenant schemas, "does every index lead with tenant_id?" is audit question number one.)

Covering indexes: skipping the second trip

A normal index lookup is two steps: find matching entries in the index, then fetch the full rows from the table. If the index contains every column the query needs, step two vanishes — the "index-only scan" that turns hot-path queries into pure index reads:

-- Postgres: INCLUDE carries payload columns without sorting by them
CREATE INDEX idx_orders_list ON orders (tenant_id, created_at DESC)
    INCLUDE (status, total_minor);

-- MySQL: put them at the composite's tail for the same effect
-- The list query now never touches the table at all:
SELECT status, total_minor FROM orders
WHERE tenant_id = 7 ORDER BY created_at DESC LIMIT 25;

Reserve this for genuinely hot queries — covering indexes are wide, and wide indexes cost memory and write throughput. Two other specialists worth knowing: partial indexes (Postgres: CREATE INDEX ... WHERE status = 'pending' — tiny index over the 2% of rows you actually query, brilliant for queue-ish tables) and expression indexes for the computed lookups mentioned above.

Reading EXPLAIN without a priesthood

You don't need to parse every field; you need to spot four things in EXPLAIN ANALYZE output:

1. Seq Scan / type: ALL on a big table in a hot query — the headline finding. (On a 200-row table it's fine and faster than an index; don't "fix" those.)
2. Rows estimated vs rows actual wildly diverging — the planner's statistics are stale (ANALYZE the table) and every downstream choice is built on fiction.
3. Sort nodes (Using filesort in MySQL) on user-facing paths — a missing ORDER BY-compatible index.
4. Nested loops over large row counts — often the ORM's fault, not the planner's.

And where do candidate queries come from? Not from guessing: Postgres's pg_stat_statements or MySQL's slow query log, sorted by total time (a 50ms query running 10,000×/hour beats the scary 4-second nightly report). That sorted list plus this post is the whole methodology.

The maintenance contract

Indexes are living infrastructure: check usage stats quarterly (pg_stat_user_indexes — unused indexes are pure write-tax; I routinely delete a third of what I find), add new ones CONCURRENTLY in production, and re-verify plans after major version upgrades — planners change their minds. The steady state to aim for: every hot query has a purpose-built path, every index has a query that justifies it, and EXPLAIN holds no surprises. Boring, fast, and cheaper than any hardware you were about to buy.

Slow query log burning a hole in your dashboard? An indexing audit is the single highest-ROI engagement I offer — bring the top ten, leave with the fixes.