Welcome to WebmasterWorld Guest from 54.145.58.37

Forum Moderators: open

Message Too Old, No Replies

Mysql - How to select a random row?

what is the best way to select a random row from a mysql table

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

New User

5+ Year Member

joined:Feb 12, 2007
posts:10
votes: 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?
11:50 pm on July 22, 2010 (gmt 0)

Senior Member

WebmasterWorld Senior Member 10+ Year Member

joined:May 31, 2005
posts:1108
votes: 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.
5:24 pm on July 23, 2010 (gmt 0)

Senior Member

WebmasterWorld Senior Member rocknbil is a WebmasterWorld Top Contributor of All Time 10+ Year Member

joined:Nov 28, 2004
posts:7999
votes: 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;
3:42 am on Aug 7, 2010 (gmt 0)

Senior Member

WebmasterWorld Senior Member txbakers is a WebmasterWorld Top Contributor of All Time 10+ Year Member

joined:Sept 1, 2001
posts:4392
votes: 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.
5:37 pm on Aug 7, 2010 (gmt 0)

Senior Member

WebmasterWorld Senior Member rocknbil is a WebmasterWorld Top Contributor of All Time 10+ Year Member

joined:Nov 28, 2004
posts:7999
votes: 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.
9:32 pm on Aug 7, 2010 (gmt 0)

Senior Member

WebmasterWorld Senior Member txbakers is a WebmasterWorld Top Contributor of All Time 10+ Year Member

joined:Sept 1, 2001
posts:4392
votes: 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.
 

Join The Conversation

Moderators and Top Contributors

Hot Threads This Week

Featured Threads

Free SEO Tools

Hire Expert Members