How to choose a random record out of the greatest N records efficiently(not using sub query) in MySQL?

Question

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

The above needs to create a temporary table each time,which is not efficient,so it doesn't qualify.

How to do it properly?

Answer 1

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;

Answer 2

If I understand correctly, you want the Rth row from an ordered result set where R is a random number. If so, then it seems the LIMIT option with two parameters is the one you want. The first parameter could be the random number from 1 to N:

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

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