There are a few things here…
- The order of a result set from a query is not guaranteed because it has an index. In general with SQL you cannot assume any particular order of the result set unless you specify the order by clause.
- The advantage of creating a b-tree index when using order by is that you can traverse the index in the same order you want the results to be in. This is faster than scanning the table which most likely won’t be stored in the same order.
- All b-tree indices are sorted (it helps ensure the lookup is fast). It just depends on if you want it sorted in ascending or descending order.
