Faster to randomize many results on MySQL Server or in PHP

Question

Is it faster for the randomize the sort order of a large set of results on the MySQL server or after I have received the data in PHP?

Does anyone have any data to backup either side, or even anecdotal experience?

Answer 1

My educated guess is that MySQL wins hands down. It's made to handle data this way. The sheer overhead of having to move all the data over to PHP for sorting gives it an advantange to boot.

You can find out for yourself by doing some benchmarking, of course.

Answer 2

Using RAND() in MySQL will definitely be faster.

To randomise via PHP you would have to query the db and push the data into memory, so you have a whole extra step adding overhead to your sort process.

In MySQL, this type of query will not be as fast as ordering by an indexed value, but it will still be highly optimised.

Answer 3

Note that you use rand() as the object of the order by statement:

  select * from foo_table order by rand() limit 100

Answer 4

For sure you should do it on MySQL side.

But note that ORDER BY RAND() is very inefficient in MySQL since it requires filesort.

See how to select 10 random rows efficiently with a single table scan:

SELECT  *
FROM    (
        SELECT  @cnt := COUNT(*) + 1,
                @lim := 10
        FROM    t_random
        ) vars
STRAIGHT_JOIN
        (
        SELECT  r.*,
                @lim := @lim - 1
        FROM    t_random r
        WHERE   (@cnt := @cnt - 1)
                AND RAND() < @lim / @cnt
        ) i

See this article in my blog for performance details:

• Selecting random rows

For a 1,000,000 records table, it runs in less than a second instead of 15 seconds using ORDER BY RAND().