Rails Performance: Lessons from Production — #2
"Slow query? Add an index" is something everyone has heard. But I once hit a more embarrassing situation: I added the index, and the query didn't get any faster. Debugging that forced me to actually understand how indexes work — when they're lightning fast, and when they simply won't be used. This post walks through it with one example: a shipments table with a few million rows.
The shipments table has 3 million rows. The front end looks up one row by tracking number: Shipment.where(tracking_no: "ABC123") — 12 seconds slow. I added an index and it dropped to 8 milliseconds. One line of CREATE INDEX, a thousand times faster. Felt great.
Until another query, where I added an index the same way, and EXPLAIN still showed a full table scan — no faster at all. That's when it clicked: an index isn't magic that makes things fast just by existing. It has its own rules — and if you don't understand them, adding one does nothing.
Without an index, the DB can only find a row by scanning from the first row to the last, comparing tracking_no one by one. With 3 million rows that's 1.5 million on average — like a book with no index, where finding a word means flipping through the whole thing.
