At 02:08 AM 9/20/2002, you wrote:
>Hi all,
>
>
>anyone knows a good method to retrieve 1 random selevted record out of
>thouseands?
>
>right now I'm using
>
>select ID from table ORDER BY rand() LIMIT 1
>
>however, this seems like a very slow method, like mysql first organizes a
>few thousand records in a random 1 and only returns 1....
>
>Any help would be great!
>
>thx. Wilbert
Wilbert,
It can't be done using just SQL statements (unless you cheat). It
all depends on how random the results have to be.
The long way:
You need to write code in PHP or whatever language you're using. As you've
found out, the Order By Rand() physically sorts the table which can be
extremely slow if you have thousands of rows. So the solution is to have a
table with an auto incrementing column, like rcd_id. You need to retrieve
the first (1) and last number of this range (90,100). Create a loop and
inside this loop create a random number (say 42,132) within this range.
Then check to see if Rcd_Id=that random number. If it does, then you're got
your record and you can exit the loop. If the rcd_id is not found, then you
need to generate another random number and try again. The reason the rcd_id
isn't found is if the row was deleted. Now a lot of people might think you
can simply do a "select * from table where rcd_id >= ceiling(90100*rand())
limit 1" but that won't work if you have holes in your rcd_id sequence.
Example, say rcd_id's 10 through 19 are deleted and you have rcd_id's
1..10, and 20. Then rcd_id=20 will be picked half the time because it also
gets selected if the random number is in the range 11..19.
The easy way (cheat):
Ok, the easiest way is to cheat.<g> Add a Float (or Double if you have a
very large # of rows) column to your table and make it an index. Now each
time a row is added, add a Rand() to that column. Now each row has a random
number 0 <= rand < 1. To get a random row just do "Select * from table
where RandCol >= Rand() limit 1". This will give you pretty good random
results. We are assuming of course that duplicate random numbers would be
quite rare and in most cases this fudge factor won't matter. This all
depends on how good the Rand() function is at creating a proper
distribution of random numbers. The RandCol must be indexed so it traverses
the index, otherwise it will find rows at the beginning of the table every
time. This method should be quite fast and reasonably random. The overhead
is of course an extra column and index.
I hope this helps. :)
Mike
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
