Part 3 of 5 · 1 min read
Sorting via an existing index
A B+-tree index already stores its keys in sorted order, so reading it in order avoids any separate sort step.
Think of a phone book that is already alphabetized. Reading through it from the front produces a sorted list automatically, with no separate sorting step needed at all.
In plain words: a B+-tree index, covered in Level 5, stores its keys in sorted order across its leaf chain. If a query's ORDER BY column matches an existing index's key order, the database can read rows directly off that index's leaf chain, in order, and skip sorting entirely. This is often far cheaper than computing the sort from scratch, especially for a large result set.
A worked example. SELECT * FROM orders ORDER BY placed_on;, with an index on placed_on, reads rows directly off that index's sorted leaf chain, with no separate sort step at all.
The key lines.
CREATE INDEX idx_orders_date ON orders (placed_on);
SELECT * FROM orders ORDER BY placed_on;
-- the index's existing sorted order satisfies ORDER BY directly
Index-satisfied ORDER BY
An index-satisfied ORDER BY happens when a query's sort column already matches an existing index's key order, letting the database read rows directly off the index in order and skip a separate sort step entirely.
Example: reading an already-alphabetized phone book from the front produces sorted output automatically, the same way reading an index in order satisfies an ORDER BY without sorting.