The model
A database index is an auxiliary structure that helps find rows without scanning every table page. A B-tree keeps keys in sorted order across pages and uses separator keys to direct a search from the root toward a leaf. The index is valuable when its lookup cost is lower than the work it avoids.
A concrete walk-through
For a query filtering by customer_id and ordering by created_at, a composite index beginning with customer_id may narrow to one customer’s key range and then return rows in order. If the query selects columns present in the index, some engines may avoid visiting table pages for each result.
Costs and failure cases
Indexes consume storage and add work to inserts, deletes, and updates. A low-selectivity column may not justify an index by itself, and a query planner can prefer a sequential scan when many rows qualify. Composite index column order changes which predicates can use its leading range efficiently.
Check your understanding
Given an index on (tenant_id, created_at), compare queries filtering by tenant_id alone and created_at alone. Explain what information the index ordering makes directly available.