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

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().