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).
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.
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.
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.
Sometimes if you can avoid a "Using temporary; Using filesort" it's worth it, though I haven't done extensive testing.
1 SIMPLE favorites ref source_id,user2_id source_id 3 const 137 Using index; Using temporary; Using filesort
1 SIMPLE users eq_ref PRIMARY,updated PRIMARY 3 apm.favorites.target_id 1 Using where
Instead of asking mysql to sort by name, in ruby I do
results.sort_by {|u| u.name.downcase}
The resulting mysql query is now simpler:
1 SIMPLE favorites ref source_id,user2_id source_id 3 const 137 Using index
1 SIMPLE users eq_ref PRIMARY,updated PRIMARY 3 apm.favorites.target_id 1 Using where
Of course you wouldn't want to do this if you returned more than a few hundred rows.
