[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