homepage Welcome to WebmasterWorld Guest from 54.197.40.130
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member
Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderators: physics

Databases Forum

    
Mysql - How to select a random row?
what is the best way to select a random row from a mysql table
asahmed




msg:4175057
 11:00 pm on Jul 22, 2010 (gmt 0)

Hello world :)

Does anybody know the best way to select a random row from a Mysql table?


I've been using the following query until someone told me that it is not the best in term of performance. especially on big tables..
SELECT * from my_table ORDER BY RAND() LIMIT 1


Any suggestions or alternative ways to select a random record?

 

Dijkgraaf




msg:4175070
 11:50 pm on Jul 22, 2010 (gmt 0)

If you have an ID field in the database you could first get a random number (with a maximum of your largest ID) and then do a
SELECT * from my_table WHERE ID = randomnumber

You might have to then check to see if you actually got a row (in case your ID's aren't consecutive) and repeat until you get a row. The less consecutive your ID field is of course the less efficient this becomes.

rocknbil




msg:4175442
 5:24 pm on Jul 23, 2010 (gmt 0)

I don't know . . I use it, it's never been a problem . . . but I suppose they are right. :-)

If you have an ID field in the database you could first get a random number


The only problem with that is, what if a deleted record's random number is selected?

Some ideas:

select count(*) from rows, pick a random number in between 1 and $count, then

... order by whatever limit $count, 1

Even if "$count" has been deleted, it will pull the next record starting at index $count.

create a second table with a single int() column, populate it with existing record handle id's. As new records are added (and deleted, remove them), drop the id into this table. Do your rand on that, then select the result

.... where id=$rand_id;

txbakers




msg:4183457
 3:42 am on Aug 7, 2010 (gmt 0)

1) select count(id) from rows
2) generate a random number between 1 and the result of the query
3) select * from table where id = new randomnumber
if !EOF show data
else run query again
etc.

rocknbil




msg:4183656
 5:37 pm on Aug 7, 2010 (gmt 0)

See previous, what if that record id no longer exists?

Though it **shouldn't** happen, there is a potential for a recursive infinite loop there.

txbakers




msg:4183707
 9:32 pm on Aug 7, 2010 (gmt 0)

Once you have the count(id) you can setup a loop for that number of times.

then, when your query on the id is NOT EOF, you can break. If you never have a hit the loop will just end on the count.

you use also should the MAX(id) to give you the highest id. So your loop would be:
for (var i = 1; i < count(id); i++) {
sql here - select * from table where id = the random num between 1 and max(id)
if !EOF {
set your variable to the value you want
break loop
}

that way you don't have an infinite loop and you can have a random integer up to the highest value in there.

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved