Random Results

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
agriz
Forum Contributor
Posts: 106
Joined: Sun Nov 23, 2008 9:29 pm

Random Results

Post by agriz »

Hi,

How to bring random results from mysql without using RAND()
The table might have the following records.

Name, category_id

I want 10 random records for particular category_id
User avatar
Christopher
Site Administrator
Posts: 13596
Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US

Re: Random Results

Post by Christopher »

A simple way it to use PHP to pick a random number between 0 and COUNT(*)-10 and then set the OFFSET to the random number and LIMIT 10. You could also randomly set the sort on various columns and directions. That will give you 10 records in one query. Or you can do 10 queries.
(#10850)
agriz
Forum Contributor
Posts: 106
Joined: Sun Nov 23, 2008 9:29 pm

Re: Random Results

Post by agriz »

Is there any other option?

I dont like to write 10 queries.
The other option will bring 10 straight records from different positions.

Is there any other way to get record like 1, 34, 99, 1034, 4, 434, 999
User avatar
Mordred
DevNet Resident
Posts: 1579
Joined: Sun Sep 03, 2006 5:19 am
Location: Sofia, Bulgaria

Re: Random Results

Post by Mordred »

If it's a small table, and you're sure it will stay that way, you can use

SELECT * from blah ORDER BY RAND()

Another solution is to use two queries, one to get the number of items, then use a PHP-generated query consisting of 10 SELECTS with UNION
User avatar
Apollo
Forum Regular
Posts: 794
Joined: Wed Apr 30, 2008 2:34 am

Re: Random Results

Post by Apollo »

using ORDER BY RAND() LIMIT 10 in your query is really the easiest & fastest way to do this.

Alternatively you can generate 10 random IDs and select those in one query:

Code: Select all

list($n) = mysql_fetch_row(mysql_query("SELECT COUNT(1) FROM puppies"));
$ids = range(1,$n);
shuffle($ids);
$ids = implode(',',array_slice($ids,0,10));
$result = mysql_query("SELECT * FROM puppies WHERE id IN ($ids)");
If you've got a lot of records, avoiding range to generate $ids is probably faster, e.g.

Code: Select all

$ids = array();
for ($i=0; $i<10; $i++) { do { $j = mt_rand(1,$n); } while (in_array($j,$ids)); $ids[] = $j; }
$ids = implode(',',$ids);
agriz
Forum Contributor
Posts: 106
Joined: Sun Nov 23, 2008 9:29 pm

Re: Random Results

Post by agriz »

In what kind of tables i should avoid Rand()

My table has very low records. But i am adding data. It will reach about 50,000 records.
User avatar
Apollo
Forum Regular
Posts: 794
Joined: Wed Apr 30, 2008 2:34 am

Re: Random Results

Post by Apollo »

Try both methods and see for yourself which is faster.
User avatar
Mordred
DevNet Resident
Posts: 1579
Joined: Sun Sep 03, 2006 5:19 am
Location: Sofia, Bulgaria

Re: Random Results

Post by Mordred »

Apollo wrote:Alternatively you can generate 10 random IDs and select those in one query:
This will only work if you have an id column and the ids are consecutive. You could make it work by selecting more, say 20 and hope that you wouldn't hit enough "holes" and will get at least 10 results. Still it depends on the usage pattern of the table.
agriz
Forum Contributor
Posts: 106
Joined: Sun Nov 23, 2008 9:29 pm

Re: Random Results

Post by agriz »

Mordred wrote:This will only work if you have an id column and the ids are consecutive.
I have auto increment id on the table.

Code: Select all

$ids = array();
for ($i=0; $i<10; $i++) { do { $j = mt_rand(1,$n); } while (in_array($j,$ids)); $ids[] = $j; }
$ids = implode(',',$ids);
According this one, lets assume i am getting the following

1, 49000, 45, 345, 7656, 12, 3423, 20333, 5454, 343

If i have to get records for category_id ( 1 ) and if the ids are for category_id which is not 1 then the result for my query will be 0 or less than 10
agriz
Forum Contributor
Posts: 106
Joined: Sun Nov 23, 2008 9:29 pm

Re: Random Results

Post by agriz »

Apollo wrote:Try both methods and see for yourself which is faster.
The server is not dedicated. So if many people use it, and if the query took a lot of resource and hosting provider will suspend the account.
If i use rand() for myself, it will bring results faster and few seconds delay are not much important.
User avatar
Apollo
Forum Regular
Posts: 794
Joined: Wed Apr 30, 2008 2:34 am

Re: Random Results

Post by Apollo »

agriz wrote:I have auto increment id on the table.
Still it may be possible that you delete rows later on, resulting in 'missing' IDs.
To do it absolutely correct, you'd have to SELECT category_id FROM yourTable and pick 10 random IDs from that list. Sounds like more effort than it's worth.

If you really wonder, you could try for yourself if (pseudocode)

$ids = SELECT category_id FROM youTable ORDER BY RAND() LIMIT 10
SELECT * FROM yourTable WHERE category_id IN ($ids) LIMIT 10

is faster than

SELECT * FROM yourTable ORDER BY RAND() LIMIT 10

or the UNION approach mentioned by Mordred.

But still I think simply using ORDER BY RAND() to select the rows directly is really the fastest & easiest method.
agriz
Forum Contributor
Posts: 106
Joined: Sun Nov 23, 2008 9:29 pm

Re: Random Results

Post by agriz »

Code: Select all

SELECT * FROM tbl WHERE category_id = 3 ORDER BY RAND() LIMIT 10
This is the real query.

Lets assume that the whole "tbl" contains 1,00000 and every category_id contains 2,000 records

so,

Code: Select all

SELECT * FROM tbl WHERE category_id = 3
will bring 2000 records from 1,00000 records

I am worrying to use rand() because of my shared hosting. In shared hosting i should not use much resource.
User avatar
Apollo
Forum Regular
Posts: 794
Joined: Wed Apr 30, 2008 2:34 am

Re: Random Results

Post by Apollo »

Why do you think ORDER BY RAND() will consume much resources?

And even if it would (which it doesn't in a typical situation with 2000 results to choose from), I'm quite sure any of the alternatives would consume even more.

If your shared hosing account is really so limited that simple basic queries like these might get you in trouble, I'd seriously consider getting another hosting provider :)
agriz
Forum Contributor
Posts: 106
Joined: Sun Nov 23, 2008 9:29 pm

Re: Random Results

Post by agriz »

Server is not much worst.
I will go ahead with rand()

I read about rand() in some sites who recommended not to use rand at any situations.
Post Reply