order by rand, is there a better way?

[cyberkiller]

Is their a more efficient way to do this query?

 

$query="SELECT domain_id,domain FROM domain ORDER BY rand() Limit 1";

 

Expected output is one random domain_id, domain from the table.

 

Now I did read the sticky,

http://www.phpfreaks.com/forums/index.php/topic,125759.0.html

 

However to me the queries in that post looked more strainful than my current one.

[Illusion]

$query="SELECT domain_id,domain FROM domain ORDER BY rand(now()+1) Limit 1;

[fenway]

$query="SELECT domain_id,domain FROM domain ORDER BY rand(now()+1) Limit 1;

Ignore this... "more strainful"?

[XaeroDegreaz]

Well there is a more 'random' way of doing it. However it requires 2 queries.

 

One query to get the amount of results:

 

$query = "SELECT * FROM `users` WHERE `blah` = 'bleh'"

$max = mysql_num_rows($query) //This gives us our maximum value

 

srand((double)microtime() * 1000000);

$final = rand(0,$max);

 

Then you do the query again (sorry, I know there is another way, but I can't remember how to recall that query)

 

$query = "SELECT * FROM `users` WHERE `blah` = 'bleh' LIMIT $final,1" (or is it 1,$final) =}~

 

That will select a random row. That's actually what I've been using, because the rand() for mysql is totally out of line

 

Good luck.

 

[NArc0t1c]

uhm..

I have a theory that might say this could work aswell,

<?php
$Rows = mysql_num_rows(mysql_query("SELECT domain_id FROM domain"));
$Random = rand(0,$Rows); 
$Query = "SELECT domain_id, domain FROM domain WHERE domain_id='" . $Random . "'";
?>

[Illusion]

$query="SELECT domain_id,domain FROM domain ORDER BY rand(now()+1) Limit 1;

Ignore this... "more strainful"?

why?

[fenway]

Well there is a more 'random' way of doing it. However it requires 2 queries.

 

One query to get the amount of results:

 

$query = "SELECT * FROM `users` WHERE `blah` = 'bleh'"

$max = mysql_num_rows($query) //This gives us our maximum value

 

srand((double)microtime() * 1000000);

$final = rand(0,$max);

 

Then you do the query again (sorry, I know there is another way, but I can't remember how to recall that query)

 

$query = "SELECT * FROM `users` WHERE `blah` = 'bleh' LIMIT $final,1" (or is it 1,$final) =}~

 

That will select a random row. That's actually what I've been using, because the rand() for mysql is totally out of line

 

Good luck.

 

That will be insanely slow if $final is large.

 

uhm..

I have a theory that might say this could work aswell,

<?php
$Rows = mysql_num_rows(mysql_query("SELECT domain_id FROM domain"));
$Random = rand(0,$Rows); 
$Query = "SELECT domain_id, domain FROM domain WHERE domain_id='" . $Random . "'";
?>

That's only useful if you want to do something very simple, without any "true" order by clause.

 

$query="SELECT domain_id,domain FROM domain ORDER BY rand(now()+1) Limit 1;

Ignore this... "more strainful"?

why?

Because it's still running RAND() for each row, which is what makes it slow -- having a seed makes no difference whatsoever.