How Adding a Single Database Index Cut Query Latency from 200ms to Under 1ms

A developer building a URL shortener noticed severe performance degradation as traffic scaled from hundreds to thousands of requests per second. Investigation revealed the database was performing a full table scan on every lookup, reading all rows just to match a single short code string. Adding a B-tree index on the short_code column eliminated the sequential scan, allowing the query planner to jump directly to the matching row in under a millisecond. The article explains how indexes work as optimized lookup structures, comparing B-tree, hash, and specialized index types, while noting the trade-off of increased write overhead and storage costs. Key lessons include matching indexes to actual query predicates and avoiding over-indexing, which can slow down write-heavy operations like batch jobs.