Menu
If MySQL is to run a query select * from table order by datetime, where datetime is a datetime column, on a table with >10 million rows, which sorting algorithm does it use?
278
There are two different sort logics used by SQL Server to handle sorting! First one is the quick sort and another one is Merge sort. It begins sort in memory using Quick Sort algorithm but note that the memory requirement for this is at least 200% of its input rows size.
Traditionally, database sort implementations have used comparison-based sort algo- rithms, such as internal merge-sort or quicksort, rather than distribution sort or radix sort, which distribute data items to buckets based on the numeric interpretation of bytes in sort keys [Knuth 1998].
Quicksort. Quicksort is one of the most efficient sorting algorithms, and this makes of it one of the most used as well. The first thing to do is to select a pivot number, this number will separate the data, on its left are the numbers smaller than it and the greater numbers on the right.
Unfortunately, MySQL does not provide any built-in natural sorting syntax or function. The ORDER BY clause sorts strings in a linear fashion i.e., one character a time, starting from the first character.
You can find the details in the documentation on Order By Optimisation.
Essentially the MySQL engine will consider using an index, if a suitable one is available, and it is estimated that using it would be beneficial to the performance.
If no such index is selected, then a so-called "filesort" operation will be performed, which -- despite its name -- might very well execute completely in memory. But it may also use temporary files to swap in/out partitions that are (to be) sorted, and to merge sorted partitions into bigger ones.
In-memory sorting is performed with Quick Sort. You can find a mf_qsort.c file in the source files in the mysys folder.
A datetime is represented by 5 to 8 bytes (depending on whether second fractions are used), and sorting by it is no different than sorting a bigint which also occupies 8 bytes.
119
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
