How do database indexes speed up queries and what are the trade-offs of B-Tree indexing?
A database index is a data structure (predominantly a B-Tree or B+ Tree) that maintains sorted references to table rows, allowing the database engine to locate records in O(log N) time instead of performing an O(N) full table scan.
While indexes dramatically speed up SELECT, JOIN, and ORDER BY queries, they incur overhead: every INSERT, UPDATE, or DELETE statement must also update the index trees, consuming write I/O and additional disk space. Unused or duplicate indexes degrade database write performance.
-- Create a composite index optimized for multi-column filtering and sorting
CREATE INDEX idx_orders_customer_date
ON orders (customer_id, order_date DESC)
INCLUDE (total_amount);
-- Query utilizing index seek and index-only scan
SELECT customer_id, order_date, total_amount
FROM orders
WHERE customer_id = 4520
AND order_date >= '2026-01-01'
ORDER BY order_date DESC;Because the index contains `customer_id` first and `order_date` second (matching the query's WHERE and ORDER BY clauses), the query engine can perform a fast index seek without sorting in memory. The `INCLUDE` clause avoids visiting the base table completely (covering index).
Ready for Basic Queries & Filtering?
Take a quick test to evaluate yourself, or move straight to the next module.