0

I am fetching some comments from the database in DESC from 0,15 - 15,15 - 30,15 and so on and i want to show them in reverse so i use array_reverse() function.

Whats fastest? Using array_reverse or change the SQL query to reverse it

Teun Zengerink
  • 4,277
  • 5
  • 30
  • 32
stergosz
  • 5,754
  • 13
  • 62
  • 133

2 Answers2

3

Using ORDER BY field DESC is generally going to be faster and easier to read (and slightly lighter on the memory consumption of the script), but if you've already written this it's not so significant that I'd go back to re-do it.

There are some cases, however, where you'll actually want to stick with a post-query array reversal. For instance where you're ordering by an index: 

SELECT table.field
FROM table USE INDEX ([index])
WHERE  ...
ORDER BY [index] DESC

It may be much faster to use either ASC or DESC, so you'd just want to run the faster order and then reverse it afterwards: see MySQL ORDER BY DESC is fast but ASC is very slow

Also, in cases where the mysql query becomes much more complex as a result of ordering, it may be faster to reverse the array. For instance, if you want only the last three results from a query that returns five results but you want them ordered desc, you'd have to run a sub-query, like:

SELECT * FROM (
    SELECT ...
    ORDER BY field ASC
    LIMIT 3
) AS sq ORDER BY field DESC

In this scenario, it may be a lot easier (and perhaps faster) to just run:

SELECT * FROM table WHERE ... ORDER BY field DESC LIMIT 3

and the reverse the results once they are returned.

Community
  • 1
  • 1
Ben D
  • 14,321
  • 3
  • 45
  • 59
  • +1 with the exception that you should never choose your solution based on how interesting you feel the project is - not cool. – CBusBus Jan 31 '12 at 14:21
  • ha. I was referring to uncommon mysql queries where ordering matters. There are some cases (for instance: `SELECT field FROM table USE INDEX (column) WHERE...`) where ordering really matters and choosing to switch asc to desc has major effects on the query. In these cases, you'll just want to reverse the array. See: http://stackoverflow.com/questions/2886575/mysql-order-by-desc-is-fast-but-asc-is-very-slow – Ben D Jan 31 '12 at 14:26
0

If you can get away with it, i.e it doesn't affect the rest of your code, then change the query to sort by ASC.

If you can't you'll need to iterate through the result set and create an array before using something like asort() to rearrange the array.

CBusBus
  • 2,321
  • 1
  • 18
  • 26