Select optimised random results from a sub query

Question

I want to use the "order by rand alternative" query (bottom) to get a random set of results but I want to get them from within the results a query such as:

SELECT t2.id FROM index_table t1 JOIN data_table t2 ON t1.id= t2.index_id

And I need to limit the number of random results I'd get back. I can't quite get my head around the syntax I'd need to use, any help greatly appreciated.

thanks

"order by rand alternative" query:

How can i optimize MySQL's ORDER BY RAND() function?

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(20090301) < @lim / @cnt
        ) i

score 0 · Answer 1 · answered Mar 28 '12 at 16:18

0

SELECT 
      t2.id 
   FROM 
      index_table t1 
         JOIN data_table t2 
            ON t1.id= t2.index_id
   ORDER BY
     RAND()
   LIMIT 5

or whatever the maximum number of entries you want via the limit

answered Mar 28 '12 at 16:18

DRapp

47,638
12
72
142

I wanted to use the optimised method quoted and linked in the post not order by rand as it is too inefficient – DonutReply Mar 28 '12 at 16:29

score 0 · Answer 2 · answered Mar 28 '12 at 17:04

Would something like this work?

SELECT  *
FROM    (
        SELECT  @cnt := COUNT(*) + 1,
                @lim := 10
        FROM    FROM index_table t1 JOIN data_table t2 ON t1.id= t2.index_id
        ) vars
STRAIGHT_JOIN
        (
        SELECT  t2.id,
                @lim := @lim - 1
        FROM index_table t1 JOIN data_table t2 ON t1.id= t2.index_id
        WHERE   (@cnt := @cnt - 1)
                AND RAND() < @lim / @cnt
        ) i

I removed the parameter from RAND otherwise my output was always the same.

Select optimised random results from a sub query

2 Answers2