Why database indexes can fail: the B-tree mechanics every developer should know

A database index is a sorted B-tree structure that maps a specific column to row pointers, allowing queries to find data in just a few hops instead of scanning every row in a table. Without an index, a query on a million-row table performs a full table scan, checking each row individually — a process that scales linearly with table size. While indexes can cut query times from seconds to milliseconds, they come at a cost: every insert, update, or delete requires the index tree to be updated, slowing down writes. Composite indexes follow a strict left-to-right rule, meaning a two-column index on last name and first name cannot speed up searches by first name alone. Common pitfalls like leading wildcards in LIKE queries or wrapping indexed columns in functions also prevent the database engine from using the index at all.