Database Indexes Explained: B-Trees, Composite Keys, and When Indexes Hurt Performance (2026)

Database Indexes Explained: B-Trees, Composite Indexes, and When They Hurt Performance You added an index. The query got slower. Here is why, and how to use indexes correctly. How B-Tree Indexes Work A B-Tree index is a sorted data structure that lets the database find rows without scanning the entire table. Think of it like a phone book: sorted by last name, you can find "Smith" without reading every entry. SELECT * FROM users WHERE email = $ 1 ; CREATE INDEX idx_users_email ON users ( email ); Composite Indexes Order matters. A composite index on (country, city) helps queries filtering by country alone, but NOT queries filtering by city alone. -- This index helps all three queries below CREATE INDEX idx_orders_status_date ON orders ( status , created_at ); -- Uses index (leftmost prefix) SELECT * FROM orders WHERE status = 'pending' ; -- Uses full index SELECT * FROM orders WHERE status = 'pending' AND created_at > NOW () - INTERVAL '1 day' ; -- Does NOT use index (skips leftmost col