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

-------------Problems Reply------------

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.

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.

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.

ORDER BY will almost always be faster.

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

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.

Category:php Views:0 Time:2009-03-17
Tags: php sql arrays

Related post

  • SQL: Proper way to sort array of two fields? (without subquery) 2012-03-19

    There is no rhyme or reason for this question other than I was curious about how one would go about doing this and I don't typically use arrays all that often when working with databases. Platform: while I was hoping for a SQL-Standard solution, my m

  • Sort arrays of primitive types in descending order 2008-10-18

    I've got a large array of primitive types (double). How do I sort the elements in descending order? Unfortunately the Java API doesn't support sorting of primitive types with a Comparator. One workaround would be to sort and then reverse: double[] ar

  • Nokogiri: Sort Array of IDs according to order in HTML document 2009-05-06

    I have an unsorted Array holding the following IDs: @un_array = ['bar', 'para-3', 'para-2', 'para-7'] Is there a smart way of using Nokogiri (or plain Javascript) to sort the array according to the order of the IDs in the example HTML document below?

  • Sort array in ascending order while minimizing "cost" 2009-07-24

    I'm taking comp 2210 (Data Structures) next semester and I've been doing the homework for the summer semester that is posted online. Until now, I've had no problems doing the assignments. Take a look at assignment 4 below, and see if you can give me

  • php sorted array and readfile not reading in order 2010-11-05

    I am trying to write a php function to save and then display comments on an article. In my save.php, I am formulating the file with: $file = "article1/comments/file".time().".txt"; Then using fwrite() to write to a directory. In my index I have: if (

  • How to effectively sort array of ordered sequences 2011-02-06

    I am implementing the complex algorithm which part is sorting array of ordered sequences of numbers. The whole algorithm should be nlog(n) complexity, so this part should be same or better but I don't know how to do this. There is an example. There i

  • Better way to sort array in descending order 2011-03-25

    I have a array of int which I have to sort by descending. Since I did not find any method to sort the array in descending order.Currently I am sorting the array in descending order as below int[] array = new int[] { 3, 1, 4, 5, 2 }; Array.Sort<int

  • Javascript: sorting arrays that contain dates into descending order 2011-05-30

    I have the following array: var times = [ ["04/11/10", "86kg"], ["05/12/11", "90kg"], ["06/12/11", "89kg"] ]; I want to list these dates and their corresponding weights in ascending order. I know you can sort arrays with sort and I found the followin

  • How to sort a huge array of items by a specific order defined by an array? 2011-06-29

    i need to implement some specfic sorting algorithm. I have two arrays: $items = array( array("id" => "…", "type" => "alpha"), array("id" => "…", "type" => "beta"), array("id" => "…", "type" => "company"), array("id" => "…", "type

  • Sort Array Subkey Based on Another Array's Order 2011-07-15

    I know there have been many posts regarding array sorting, but I have looked high and low and cannot find a solution to my problem. I have found a very good article here: http://firsttube.com/read/sorting-a-multi-dimensional-array-with-php/ The above

  • Sorting array of records in ascending order 2011-11-05

    Possible Duplicate: How to sort an array of objects? Given the array of record of students below - how would you sort them in ascending order using Javascript according to age? students = [{ name: "timothy", age: "9"}, { name: "claire", age: "12"}, {

  • sort array value in alphabetical order 2011-12-21

    Possible Duplicate: How do I sort a multidimensional array in php I have value array and want to sort the value based on alphabetical order my current array $original_array = array( array('id' => 123456, 'name'=>michael), array('id' => 22222

  • Sort Array By variable in the first then sort it descending order 2011-12-30

    Hey guys here is my question. im using JCombobox. if a student have CB grade in the lesson,it should be appear in the jcombobox first , like that CB AA BA BB CC DC DD FD FF if student have DD grade it should be shown like that DD AA BA BB CB CC DC FD

  • Sort Array in assending order..? 2011-04-28

    Sorry for asking silly question.. I have an array with class objects like: Class User { NSString *firstName, *LastName; } @property (nonatomic, retail) NSString *firstName; @property (nonatomic, retail) NSString *LastName; I am creating object of thi

  • How do I sort arrays using vbscript? 2008-11-06

    The question says it all really, but... I'm scanning through a file looking for lines that match a certain regex pattern, and then I want to print out the lines that match but in alphabetical order. I'm sure this is trivial but vbscript isn't my back

  • Sort array by object property in PHP? 2009-09-22

    If I have an object as such: class Person { var $age; function __construct($age) { $this->age = $age; } } and I have any array of Persons $person1 = new Person(14); $person2 = new Person(5); $people = array($person1, $person2); Is there an easy wa

  • Sorting array elements 2010-05-26

    I want to write a stored procedure that gets an array as input parameter and sort that array and return the sorted array. Kindly help. --------------Solutions------------- The best way to sort an array of integers is without a doubt to use the intarr

  • Using ORDER BY to sort data 2011-03-15

    This is the data that I would like to sort. AAAAAAAA 0.0.0.0 hs01.stuff.net BBBBBBBB 0.0.0.0 hs01.morestuff.net CCCCCCCC 0.0.0.0 hs01.evenmorestuff.net DDDDDDDD 0.0.0.0 hs01.stuff.net EEEEEEEE 0.0.0.0 hs01.stuff.net FFFFFFFF 0.0.0.0 hs01.evenmorestuf

  • Dynamic SQL ordering help 2011-06-22

    Hey Guys i have looked around but have had no luck in solving this issue. I have a posts/comments database that I am unable to order correctly. I need it to be ordered primarily by its id but if its parent_id does not equal its id, it is placed after

Copyright (C) dskims.com, All Rights Reserved.

processed in 0.228 (s). 11 q(s)