Database Indexes & Slow Queries

The catalog that got slow

A shop's product list is instant for a year. Then it slows to eight seconds. Nothing about the code changed — only the number of rows. The query is the same one it has always run; the database simply stopped having any shortcut through the table, because there was never an index on the column being filtered.

What an index actually is

An index is a second, sorted structure — usually a B-tree — that maps column values to row locations. Without one, filtering WHERE status = 'shipped' reads every row and throws most away: a sequential scan. With one, the engine seeks straight to the matching rows. The database is not doing magic; it is skipping work.

The cost is real, so indexes are not free: every INSERT and UPDATE must also update each index, and indexes consume disk. You are buying read speed with write cost and space.

Column order is the whole trick

With one column, an index is simple. With two, the order decides everything: a composite index on (user_id, created_at) works for "find this user's rows" and "find this user's rows, newest first". It does not help "find rows created after January", because the leading column is unconstrained.

The rule interviewers like: leftmost prefix. Design the composite index around the query you actually run most often, most selective column first.

Let EXPLAIN tell you, not your memory

Never guess why a query is slow. EXPLAIN ANALYZE runs the query and shows the plan the engine chose:

EXPLAIN ANALYZE
SELECT * FROM orders WHERE user_id = 7 ORDER BY created_at DESC LIMIT 20;

Two words to look for. Seq Scan means a full table read — fine on 200 rows, a disaster on 20 million, and the usual answer is "you need an index". Rows Removed by Filter: 4,999,980 is the same warning written out in full. Read the estimated rows, not just the time; a plan that looks fast because the engine guessed wrong is a trap.

The N+1, which is not an index problem

Sometimes the query is fine and the number of queries is the bug. Render 500 products by asking the database once per product: 501 round trips, 8 seconds. Same indexes, still slow. Fix it with a join, an eager load, or one WHERE id IN (...) batch. Measure query counts, not just query speed.

Remember this

• No index on the filtered column means a sequential scan. That is the single most common cause of "it used to be fast".
• Composite indexes obey leftmost prefix: lead with the column you filter on most.
• EXPLAIN ANALYZE beats guessing, and a sequential scan over a large table is the finding.
• 500 fast queries still cost 500 round trips. Watch query counts too.