Hi. 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
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).
In the hypothetical case that you actually get the records in the memory of your application then mysql will still beat the pants of your app because if you configured your database right it won't HAVE to sort.
I fyou want to order by in a table of 1 Mio records, you would provide in index which would be typically implemented as a B-Tree where Mysql can walk through and get the sorted results.
MySQL will win. One more reason besides the others listed is that, assuming the records are already in the DB, you don't have to copy them out of the DB to sort them. And paging or subindexing them will be easy and optimized automatically.
In short, if the DB CAN do it, the DB SHOULD do it, almost always.
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.