Re: random sorting - what is the quickest method?

[nick rulez]

While I wait for some advice I'm always searching on google.

I found this query:

select table.* from 
(select floor (rand() * (select count(*) from table)) num ,
@num:=@num+1 from (select @num:=0) a , table limit 1) b ,  table where b.num=table.id;

that is very quick. Unluckily it works fine only if there aren't gaps between ids.
I tried it on a very small table with a lot of gaps inside it and it can occur that it couldn't find any record.

edit. Google led me here

http://explainextended.com/2009/03/01/selecting-random-rows/

This query is very interesting because it works even with gaps and it returns me how much records as I expect.

select sql_no_cache i.*
from    (
        select  @cnt := count(*) + 1,
                @lim := 5
        from    table
        ) vars
straight_join
        (
        select  r.*,
                @lim := @lim - 1
        from    table r
        where   (@cnt := @cnt - 1)
                and rand(now()) < @lim / @cnt
        ) i

I tested it on a myisam table with exactly 1 million of records and it takes an average of 0.2 seconds.

The same query on a table (always myisam) with 6 million of records takes 1.2 seconds.
Is this the best query possible or are there better alternatives?



Edited 1 time(s). Last edit at 02/04/2011 11:08AM by nick rulez.