I would like to show random 5 results from all rows.
If I try LIMIT 5 without ORDER BY, it is for 2000% faster than using ORDER BY rand() LIMIT 5. But I need to show random 5 results and not first 5 results.
I was thinking to first do a query SELECT COUNT and than use my script to get random number in a range of number of rows and than use LIMIT in another query. But also COUNT takes similar time as selecting all rows and using ORDER BY.
Is there any better solution than using ORDER BY rand()?
run a simple query to get MIN(id) and MAX(id), then use php to generate a dozen or so potential numbers randomly inside this range, and use those to pull rows – no ORDER BY, no LIMIT
the reason for “dozen or so” is because you want to allow for deletions, i.e. id numbers that aren’t there anymore, and of course pulling a dozen rows is only a teensie weensie bit more work than pulling five, and you wanna make sure you get at least five
It takes about the same time to make SELECT MAX(k2.who) or to make SELECT COUNT(k2.who). Also I can not use MAX id because ids won’t be by increment order.
What do you think about the following solution:
$counter=1;
$randnumber=100000;
while($counter>0){
$random=rand(5,$randomnumber);
$result=mysql_query("SELECT who .... WHERE who>$random LIMIT 5");
$numrows=mysql_num_rows($result);
if($numrows>4){
//OK print results
$counter=0
}else{
$randomnumber=$randomnumber/2;
}
}
i don’t understand what this means
what kind of primary key does the table have?
Maybe I wrong in wrong words. id will be selected depend on criteria. So ids which matches will be like 11,234,25234,23,64545 and not like 11,12,13,14. So Max doesn’t help me anything because if I select id between max and min, it can happen this id won’t match criteria as not all ids in this range are matching.
11,12,13,14 are not random
you said you wanted random
yes they are not, that is why I said not like 11,12,13,14 because I didn’t wrote in the first post enough information. I wrote 5 results from all rows but the thing is I need 5 results from all rows that are matching WHERE condition.
So SELECT id FROM tablename WHERE testing>100 AND … would give 200.000 results and they would be like 12,2354,6654,234,34, …
i think we don’t understand each other
I want to be able to submit Limit Entry Orders and the Stop Loss Orders for those Limit Entries at the same time. I dont want to wait for the Limit Entry Order to be filled before the Stop Order is entered.
Can I do this in some way?
Thanks.
dordhs, you have hijacked a thread with a completely unrelated question
perhaps you should start a new thread and provide more background information on what you’re trying to do
ernest1a: First thing you want to do, is to make sure your ORDER BY field is an index.
See how long that takes, if it’s still to slow, you will need to do this in a few queries, but for this you need a sequential primary key:
SELECT MAX(id), MIN(id) FROM foo;
If you have no holes in your sequence:
my $ids = array();
for ($i =0; $i < 5; $i++) {
array_push($ids, rand($minId, $maxId));
}
SELECT * FROM foo WHERE id IN join(',',$ids)
If you have a FEW holds in there (less than 25%), and don;t care that you always get 5 results.
my $ids = array();
for ($i =0; $i < 10; $i++) {
array_push($ids, rand($minId, $maxId));
}
SELECT * FROM foo WHERE id IN join(',',$ids) LIMIT 5
If you have allot of holes in there:
SELECT * FROM foo WHERE id > rand($minId, $maxId) LIMIT 1
UNION
SELECT * FROM foo WHERE id > rand($minId, $maxId) LIMIT 1
UNION
SELECT * FROM foo WHERE id > rand($minId, $maxId) LIMIT 1
UNION
SELECT * FROM foo WHERE id > rand($minId, $maxId) LIMIT 1
UNION
SELECT * FROM foo WHERE id > rand($minId, $maxId) LIMIT 1
If you have to have a semi-random results (say you don’t care that they are 100% random), you can do:
SELECT * FROM (
SELECT * FROM foo WHERE id > max(min((rand($minId, $maxId), $maxId - 100), $minId) LIMIT 100
) ORDER BY RAND LIMIT 5;
Just test and see what works best for you.
some good techniques in there, vali, nice job
i would point out that the part in red here isn’t actually SQL –
SELECT * FROM foo WHERE id IN join(‘,’,$ids)
however, it’s pretty clear what you meant
Thanks for all answers! I realized that I don’t even need random of all rows. I will just use LIMIT 500 and than do random with PHP.
…
$rnd=rand(1,500);
while($row=mysql_fetch_assoc($result)){
if($rnd==$counter){
echo $row[whom];
}
$counter++;
}
I hope it is not too wasteful to have 500 fetch_assocs.