Database Indexing Explained: How I Make Slow Queries 100x Faster

The most dramatic performance wins of my career have not come from rewriting code — they came from adding the right index. A query scanning two million rows becomes an index seek touching forty. Same hardware, 100x faster. Yet indexing remains the most misunderstood tool in web development.

The mental model

An index is a sorted copy of selected columns with pointers back to the rows. Sorted means the database can binary-search instead of reading the whole table. That is the entire trick — everything else is detail about what to sort and in which order.

Composite indexes: order is everything

An index on (tenant_id, status, created_at) serves WHERE tenant_id = ? AND status = ? ORDER BY created_at beautifully — and is nearly useless for a query filtering only on status. Rule: equality columns first, then the range or sort column. Most 'the index isn't working' mysteries are column-order mistakes.

Read EXPLAIN before and after

• type: ref or range is good; ALL means full table scan
• rows: the estimate of rows examined — watch it collapse when the index is right
• Using filesort / Using temporary: your ORDER BY or GROUP BY isn't covered
• In PostgreSQL, EXPLAIN ANALYZE shows actual times — trust it over intuition

The anti-patterns

Wrapping an indexed column in a function (DATE(created_at) = ...) disables the index. Leading-wildcard LIKE '%term' can't seek. Indexing every column 'just in case' slows every write and helps no read. And an index on a boolean with two values rarely earns its keep.

Start from your slow query log, fix the top three queries, and measure. In my experience that one afternoon of work outperforms a server upgrade — and costs nothing.