Random Results
Moderator: General Moderators
Random Results
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
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
- Christopher
- Site Administrator
- Posts: 13596
- Joined: Wed Aug 25, 2004 7:54 pm
- Location: New York, NY, US
Re: Random Results
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)
Re: Random Results
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
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
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
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
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:
If you've got a lot of records, avoiding range to generate $ids is probably faster, e.g.
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)");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
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.
My table has very low records. But i am adding data. It will reach about 50,000 records.
Re: Random Results
Try both methods and see for yourself which is faster.
Re: Random Results
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.Apollo wrote:Alternatively you can generate 10 random IDs and select those in one query:
Re: Random Results
I have auto increment id on the table.Mordred wrote:This will only work if you have an id column and the ids are consecutive.
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);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
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.Apollo wrote:Try both methods and see for yourself which is faster.
If i use rand() for myself, it will bring results faster and few seconds delay are not much important.
Re: Random Results
Still it may be possible that you delete rows later on, resulting in 'missing' IDs.agriz wrote:I have auto increment id on the table.
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
Code: Select all
SELECT * FROM tbl WHERE category_id = 3 ORDER BY RAND() LIMIT 10Lets 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 = 3I am worrying to use rand() because of my shared hosting. In shared hosting i should not use much resource.
Re: Random Results
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
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
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.
I will go ahead with rand()
I read about rand() in some sites who recommended not to use rand at any situations.