Page 1 of 1

Random Results

Posted: Tue Feb 08, 2011 1:09 am
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

Re: Random Results

Posted: Tue Feb 08, 2011 2:05 am
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.

Re: Random Results

Posted: Tue Feb 08, 2011 2:35 am
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

Re: Random Results

Posted: Tue Feb 08, 2011 3:25 am
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

Re: Random Results

Posted: Tue Feb 08, 2011 3:27 am
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);

Re: Random Results

Posted: Tue Feb 08, 2011 3:37 am
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.

Re: Random Results

Posted: Tue Feb 08, 2011 3:56 am
by Apollo
Try both methods and see for yourself which is faster.

Re: Random Results

Posted: Tue Feb 08, 2011 4:02 am
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.

Re: Random Results

Posted: Tue Feb 08, 2011 4:22 am
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

Re: Random Results

Posted: Tue Feb 08, 2011 4:30 am
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.

Re: Random Results

Posted: Tue Feb 08, 2011 4:36 am
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.

Re: Random Results

Posted: Tue Feb 08, 2011 4:45 am
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.

Re: Random Results

Posted: Tue Feb 08, 2011 5:31 am
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 :)

Re: Random Results

Posted: Tue Feb 08, 2011 5:37 am
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.