CODEWITHSUNDEEP
phpsqlarrays
user35288
user35288

Reputation:

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

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

Upvotes: 7

Views: 10649

Answers (6)

Frank Farmer
Frank Farmer

Reputation: 11

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.

Upvotes: 1

karim79
karim79

Reputation: 342625

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

Upvotes: 1

Bill Karwin
Bill Karwin

Reputation: 562250

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.

Upvotes: 3

Ward Werbrouck
Ward Werbrouck

Reputation: 1462

ORDER BY will almost always be faster.

Upvotes: 1

atfergs
atfergs

Reputation: 1684

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.

Upvotes: 1

Quassnoi
Quassnoi

Reputation: 425261

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.

Upvotes: 13

Related Questions