php/SQL: ORDER BY or sort($array)?

Question

Which do you think is faster in a php script:

$query = "SELECT... FROM ... ORDER BY first_val";

or

while($row = odbc_fetch_array($result))
    $arrayname[] = array( "first_key" => $row['first_val'],
                          "second_key" => $row['second_val'],
                          etc...
                         );

sort($arrayname);

Answer 1

It depends on so many factors that I don't even know what to begin with.

But as a rule, you perform sorting on database side.

Indexes, collations and all this, they help.

Answer 2

If the ordered field is indexed, I'd say probably the SQL query. If not, I'm not sure, but I can't imagine it will be overly noticeable either way unless you're dealing with an absurdly large number of rows.

Answer 3

ORDER BY will almost always be faster.

Answer 4

Which do you think is faster in a php script:

The ORDER BY doesn't execute in the PHP script -- it executes in the database, before data is retrieved by the PHP script. Apologies if this seems pedantic, I just want to make sure you understand this.

Anyway, the reason I would use ORDER BY is that the database has access to indexes and cached pages from the database. Sorting in PHP sorts the data set in memory of course, but has no access to any index.

Answer 5

In my opinion, nothing beats actually timing the thing so you really, really know for sure:

$time_start = microtime(true);

// Try the ORDER BY and sort($array) variants here

$time_end = microtime(true);
$time = $time_end - $time_start;

echo "It took $time seconds";

Answer 6

If there's a LIMIT on the first query, and the set of rows the query would match without the LIMIT is much larger than the LIMIT, then ORDER BY on the query is DEFINITELY faster.

That is to say, if you need the top 50 rows from a 10,000 row table, it's much faster to have the database sort for you, and return only those top 50 rows, than it is to retrieve all 10,000 rows and sort them yourself in PHP. This is probably representative of the vast majority of what will happen in real-world applications

If there are any cases at all in which sorting in PHP is even comparable, they're few and far between.

Additionally, SQL sorting is much more powerful -- it's trivial to sort on multiple columns, subqueries, the return values of aggregate functions etc.