Indexes Are Already Sorted
A B tree index stores keys in sorted order. That ordering is useful not only for lookups but also for queries that need rows in order or in groups, because the database can read the index in sequence instead of sorting afterward.
How It Helps
- An ORDER BY that matches the index key order can be served by an index scan with no sort step.
- A GROUP BY can use the same ordering to find group boundaries as it streams, avoiding a hash or sort.
- The match must respect the leading columns of the index in order, just like for filtering.
Limits To Watch
- If the sort direction or column order does not match the index, the engine falls back to an explicit sort.
- Mixing ascending and descending across columns may need an index defined with matching directions.
- A filter on a non leading column can still force a sort because the streamed order is no longer guaranteed.
Why It Matters
Removing a sort turns an operation that must buffer and order the whole result into a streaming one, which lowers memory use and lets LIMIT stop early.
Key idea
An index whose key order matches ORDER BY or GROUP BY lets the engine stream rows in order and skip a separate sort, saving memory and enabling early stop.