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);

Solution 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.

Solution 2

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.

Solution 3

ORDER BY will almost always be faster.

Solution 4

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";

Solution 5

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.

View more solutions

Comments

[Learning]

+1. Pitching your code against database just leads to heartbreaks.

[Admin]

Thanks for the clarification...I do understand, just tried to give ya'll a little context.

[Quassnoi]

Actually, there are cases when the client side sort is faster, and I even used this in one of my applications. Timing tests will fail on ellipses in the SELECT and FOR clause, and we don't know what to substitute for them.