PHP sorting is better than MySQL Sorting?

[ramoneguru]

I've had a few people come to me and tell me that they are able to sort huge amounts of data better in PHP than MySQL (as to how they go about that I didn't inquire since I thought it to be a little odd).

Now, I know if you're returning only 100's or even 1000's of rows then a simple ORDER BY clause will work fine (better than developing a sorting algorithm in PHP).

However, I can't seem to see where PHP would be better at sorting than MySQL. Does anyone have any experience with PHP being a better choice to sort something than MySQL?
--Nick

[jatar_k]

better seems a little broad

for some things I use php to chunk through the data and sort/split/chunk but I would say most of the time mysql is better

the problem with people saying that to you is they need to qualify what they mean

for a standard website, things that would come into play

dedicated or shared server
what are the specs on the server
how much avg daily traffic does the site get
how much traffic does the page get
what type of data are you trying to sort
how much data are you trying to sort
what are the sort criteria
are you using the same number of queries in your comparison

and I could probably think of a ton more criteria that would cause blanket statements to fall on their face ;)

[ramoneguru]

I was thinking along similar lines as well. Ok, maybe that was too broad a question........How about this:
When would it be absolutely necessary to sort a record set in MySQL?
--Nick

[jatar_k]

I think you should always sort in mysql unless you identify a bottleneck which could be eased by sorting in php

my approach is: best tool for the job, always

I would always sort in mysql first, if I could identify an issue with that sort, then I would look at alternatives

though I guess to answer the question exactly

>> When would it be absolutely necessary to sort a record set in MySQL?

never

nothing is absolutely necessary, but some things are more foolish than others ;)

[spinnercee]

That is a million-dollar kind of issue: I guess speed is everything == In general mySQL can return results faster if you do not have an ORDER BY clause in the query, and supposedly, a PHP array is entirely in RAM, so it may be faster to manipulate a result set in PHP, especially in the case where your goal is to combine information from multiple queries or data form sources other than mySQL (disk files, HTTP, etc...). I also like to use mySQL HASH tables because their data is always in RAM, and retrieval is very fast -- they are obviously limited by the amount of RAM you can give to them.

The load on the server, or the differential performance of the mySQL server vs the PHP server when they are on different CPUs is probably important, I would perform the sort where it returns the fastest, if that's what I was looking for -- on the other hand, I also wouldn't want a large return set from a databse that requires sorting to degrade the performance of my PHP host which is doing other things besides fronting a mySQL database.

You have options, testing always gives you answers.

[ramoneguru]

Perfect, exactly the responses I was looking for. Thanks.
--Nick

[spinnercee]

Just don't forget the impact that mySQL INDEXes/KEYs have on results -- an ORDER BY clause that is optimized with an indexed field will not be sorted, since the INDEX effectively pre-sorts the result set. That's more of a database design issue, but the sole purpose of INDEXing is to improve run-time performance, as the INDEX orders are maintained on the fly, and not generated upon every query, but only updated when the table is UPDATEd. In that case, there is no [little] overhead in returning the mySQL result ORDERed by INDEX, such that post-sorting with PHP on an unORDERed mySQL result set would be less efficient than just returning the set ORDERed BY mySQL.

The database design and your control over it may dictate what method you should use for any particular query.