Menu
I was wondering if, in terms of performance and considering a mysql select on a table with very very very many (> 1.000.000) records, is better sorting results with sql "order by" or sorting results after the query with classical programming sort algorithm ... someone has any suggestion?
Tanks
709
MySQL is faster in scope of SQL query. PHP is faster in PHP code. If you make SQL query to find out SQRT() it should be definitely slower (unless PHP is broken) because MySQL parser and networking overhead.
For sorting, PHP uses an implementation of quicksort that can be found in Zend/zend_sort. c , which takes a comparison function and an array of elements. The default comparison function for sort() is defined in ext/standard/array.
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.
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.
mySQL, hands down. It's optimized to do this, and can make use of indexes. This would be horrible to do in PHP (and you would reach the memory_limit quickly).
97
You're comparing a system with methods implemented in optimized C, meant to do exactly this task, with another that you are going to implement in an interpreted scripting language.
Basically, anything written in C is going to be a lot faster than an equivalent function written in PHP, by a factor of 10 to 100.
As already noted, there's no question at all that it is far more efficient to configure your DB properly and let it do the work.
28
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
