[thelist] MySQL select random row [WAS: MySQL queries and AUTO_INCREMENT]

mattias at thorslund.us mattias at thorslund.us
Fri Apr 7 15:40:20 CDT 2006


Quoting Matt Warden <mwarden at gmail.com>:

> On 4/7/06, Max Schwanekamp <lists at neptunewebworks.com> wrote:
>
>> OK, so I googled around a bit just now, and these guys have a good
>> discussion:
>> http://akinas.com/pages/en/blog/mysql_random_row/
>>
>> The last one is an interesting solution.
>
> It's the same thing you suggested, just using SQL subqueries (which I
> assumed weren't available):
>
> SELECT * FROM `table` WHERE id >= (SELECT FLOOR( MAX(id) * RAND())
> FROM `table` ) ORDER BY id LIMIT 1;
>
> Except it's not quite right. It should be:
>
> SELECT * FROM `table`
> WHERE id >= (
>         SELECT MIN(id) +  ROUND( ( MAX(id)-MIN(id) ) * RAND() )
>         FROM `table` )
> ORDER BY id LIMIT 1;


That's pretty elegant. I should have mentioned subqueries too, since it's
something I work with also.

I think that would avoid forcing a scan of the entire table (or index), unlike
ORDER BY RAND() LIMIT 1.

Mattias

(OK - going back to work now...)




More information about the thelist mailing list