CODEWITHSUNDEEP
mysql
Maximus
Maximus

Reputation: 2976

Using MySQL RAND() to avoid ORDER BY RAND()

I am storing RAND() value in a table on each insert and then I run following query to get random rows from the table.

select id from test where random_value >= RAND() LIMIT 5;

There are total 456 rows in a table but the random value is only picking up first 20-25 records. I ran many times above query but never got id > 21.

You can find queries and results here.

Upvotes: 4

Views: 445

Answers (2)

Maximus
Maximus

Reputation: 2976

I found this solution and it worked perfect!

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

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

Upvotes: 0

Narek
Narek

Reputation: 3823

Your RAND() changes after every loop. You need to fix it before SELECT:

SET @r := RAND();
SELECT id, @r
FROM test
WHERE random_value >= @r
LIMIT 5

But this is not a good solution because in case of small random number you will always get same rows from DB.

For saving performance you can use this method. I tried it and it works.

Upvotes: 1

Related Questions