MySQL Random Number?

chads2k2

Hey,

I am working on an idea here and was wondering if there is a way to do this. I would like to have MySQL randomly select 10 items from a table in my database and display them.

I was going to use rand() to do it but the chances of it picking a number and it not exist would be pretty good. I could do a check to see if the entry does exist but I thought that would be too resource intensive. That brings me here, does MySQL have a random number picker and it pick the 10 that do not repeat one another?

Thanks,
Chad R. Smith

eporue

You only have to look at the total number of records in the table, and them do a rand() between 0 and the resulting number of records. So if you add more records, the rand() funtion will include them, and so on.

chads2k2

Ok let's say that I have 10 records total.

so I do: rand(1,10);

Actual records: 1, 3, 5, 9, 10;

If rand picks the number 4 then it will not return a valid record. Get my drift? Now I could check to make sure the record is valid by doing a mysql_num_rows() on it to make sure it's a valid return but imagine picking ten numbers and making sure that they don't repeat. Think of how many checks that would be such as keeping track of all the numbers already used in the rand function, and then check each one to make sure the number isn't invalid.

Any other ideas? I am stumpted which is a bit odd.

Thanks,
Chad

toplay

This is how the SQL would look like to select 10 random rows from a table:

SELECT * FROM table_name ORDER BY RAND() LIMIT 10

hth.

chads2k2

Good job toplay that works perfectly. Where did you find that? I have been looking all morning on the mysql developer site and didn't find it. Did I overlook it?

Thanks,
Chad

toplay

See MySQL manual page on RAND():

http://dev.mysql.com/doc/mysql/en/mathematical-functions.html#id3075474

🙂

chads2k2

Yep, I feel dumb. Thanks toplay, I appreciate it.

Chad

scottcarp

I'm trying to do the same thing as Chad was (long ago!), but rather than DISPLAY the result, I want to update another table:

UPDATE targetTable SET targetCol = (SELECT randomCol FROM randomTable ORDER BY RAND() LIMIT 1)

It works, but takes 20 minutes to update 3000 records.

randomTable is a list (100k records) of random numbers selected from a normal distribution. I'm doing it this way because RAND() picks from a uniform distribution. If the equivalent of R's RNORM exists in MYSQL, I'd much rather use that.

Thanks,
Scott

Sxooter

order by rand() does not scale well. You'll need to use some basic offset / limit with a random number type scheme.

scottcarp

Thanks. That seems to be the consensus.
I think I'll just extract the #'s with
SELECT randomCol FROM randomTable ORDER BY RAND() LIMIT x
into Perl and stuff them back into the target table w/x update statements. That takes almost 3 whole seconds!

Sxooter

scottcarp wrote:
Thanks. That seems to be the consensus.
I think I'll just extract the #'s with
SELECT randomCol FROM randomTable ORDER BY RAND() LIMIT x
into Perl and stuff them back into the target table w/x update statements. That takes almost 3 whole seconds!

Exactly, and you only have to do it once every so often, so then the actual select from the randomized table can be done with a single offset / limit on an index and fly.

Sxooter

Note thay you could probably just select the one column with order by random into a temp table as well, somethinge like:

create table randomizer (id int);
insert into randomizer select id from master_table order by rand();

No need for perl / php to get in the way.

scottcarp

Cool. Thanks very much.

Scott