Igor Tandetnik wrote:
P Kishor <[EMAIL PROTECTED]> wrote:
On 1/25/07, Artem Yankovskiy
select * from table1 order by random(id) limit 1
Yes, very nice, thank you. I am not familiar with the "ORDER BY
random(col)" idiom. How does this work? (It does work alright).
random(anything) produces a random number (the parameter apparently
doesn't matter). The query works by associating a random number with
every row, then picking whichever one happens to end up with the
smallest number.
This does work but it requires duplicating the entire table into a
temporary table which also has the random number assigned to each row,
and then sorting it. This is very expensive for a large table.
duplicate O(N) + sort O(N log N) + select O(1)
The offset mechanism proposed by Igor earlier is far more efficient as
long as you know the size of the table. You can always get the size from
a count query, which also requires a table scan, but even that is less
expensive than duplicating the table since it is only reading not
writing. On average the offset mechanism will scan half the table to
find the random record.
count O(N) + select O(N/2)
If your table is large this will be a lot faster.
HTH
Dennis Cote
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
