Have Geek Brain

How to correctly select a random record from the largest N records (not using a subroutine) in MySQL?

select .. from (
    Select ... from ... order by weight desc limit N
    ) order by rand() limit 1

The above has to create a temporary table every time, which is inefficient, so it doesn't fit.

How to do it right?

+2


a source to share


2 answers


If I understand correctly, you need a string Rth

from an ordered result set, where R

is a random number. If so, then it seems that the LIMIT option with two parameters is the one you want. The first parameter can be a random number from 1 to N

:

SELECT ... order by weight desc limit R,1


I don't have MySQL, so I can't test it. So I don't know if R can use RAND () directly or if it needs to be precomputed.

+1


a source


You should take a look at:

http://akinas.com/pages/en/blog/mysql_random_row/



There are several suggestions for implementing this while avoiding table scans, including:

SELECT * FROM `table` WHERE id >= (
        SELECT FLOOR( MAX(id) * RAND()) FROM `table` 
    ) ORDER BY id LIMIT 1;
0


a source






More articles:


All Articles