Start with the idea
A B-tree index turns a full scan into a logarithmic descent, which is why the leftmost-prefix rule matters: an index on (a, b) helps a query filtering on a, and on a and b, but not one filtering only on b. Every index also has to be maintained on write, so a table with ten indexes is a slow table to insert into.
An index is another data structure maintained alongside table rows so some queries can avoid examining every row. A B-tree-family index commonly supports equality, range scans, and ordered traversal. For a composite index on (account_id, created_at), queries restricted by account can often use the leading part, and a query for one account ordered by time can benefit from the second part. A query only on created_at may not use the index efficiently, though optimizers and specialized access paths vary by database.
Indexes cost storage and write work.
Keep learning, for free
The rest of this lesson is waiting for you.
Create a free account to read the complete explanation, see the infographic, explore the code, and try the exercises. All 742 lessons and algorithm steps are included.
No payment details. Your account opens the complete Academy.