Menu
I'm wondering how these work under the hood, especially for large result sets. For example, would the DB likely write the raw result to disk and then do an external sort?
I'm also wondering how this works with LIMIT...OFFSET. If the DB can't utilize an existing index it seems like the DB would have to sort the whole thing and pluck the subset of the raw result set.
516
The ORDER BY keyword is used to sort the result-set in ascending or descending order. The ORDER BY keyword sorts the records in ascending order by default. To sort the records in descending order, use the DESC keyword.
ORDER BY is usually the last item in an SQL statement. You can include additional fields in the ORDER BY clause. Records are sorted first by the first field listed after ORDER BY. Records that have equal values in that field are then sorted by the value in the second field listed, and so on.
Syntax. SELECT column-list FROM table_name [WHERE condition] [ORDER BY column1, column2, .. columnN] [ASC | DESC]; You can use more than one column in the ORDER BY clause.
We learned that SQL Server doesn't guarantee any order of the results stored in the table, nor in the results set returned from your queries, but we can sort the output by using the order by clause.
Indexes are ordered; if there's a suitable index, that will be used. Otherwise, they'll need to sort, as you suppose. The execution plan for a query (which you can get with e.g. EXPLAIN or via client menus; the exact method of getting it varies with the DBMS) may contain hints as to how a query will be sorted.
See:
134
mySQL shows their own Order By optimization on this link
Oracle shows theor own Order By algorithm procedure here
Basically, If you have an index, it is ordered. But when you don't sorting occurs which is O(n log n)
21
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
