Random rows without ORDER BY RAND()

[mrMarcus]

been a long day and i need some fresh eyes/minds...

 

i've been reading up on ORDER BY RAND() in queries being very bad for system resources.

 

what i'm trying to accomplish, and am stuck at right now, is to return two (2) records from the db, in random order, by id, with query conditions (WHERE something=1, where not all records have something equal to 1).

 

seems the conditions are uncovering holes in the returned values, thus, returning random id numbers that don't even exist in the db (either been deleted, etc.).

 

here's what i got so far .. very small.

 

<?php
$sql_low 	= mysql_fetch_array(mysql_query(sprintf("SELECT * FROM table WHERE something=%d ORDER BY id ASC LIMIT 1", (int)1)));
$sql_high 	= mysql_fetch_array(mysql_query(sprintf("SELECT * FROM table WHERE something=%d ORDER BY id DESC LIMIT 1", (int)1)));
$sql_middle	= mysql_fetch_array(mysql_query(sprintf("SELECT * FROM table WHERE something=%d AND id>=%d AND id<=%d", (int)1, (int)1, $sql_low['id'], $sql_high['id'])));

for ($doLimit=1; $doLimit<=2; $doLimit++)
{ $arr[] = rand($sql_low['id'], $sql_high['id']); }
foreach ($arr as $v)
{ echo $v.'<br />'; }?>

 

this right now is returning random records between the specified id's ($sql_low and $sql_high), but it returns any value, not necessarily ones that are legitimate in the db.

 

probably looks a little ridiculous to some, but i'm burnt out and am trying to get this finished up before the nights' end.

[jackpf]

I'm not really sure what you're doing there, but this is how you could order by rand if you don't actually want to use the mysql function. Although I'm sure it isn't resource intensive enough for anyone to actually notice...

 

function orderbyrand()
{
$sql = mysql_query("SELECT * FROM yourtable");
$count = mysql_num_rows($sql);
$rand = rand(0, $count);
$sql = mysql_query("SELECT * FROM yourtable WHERE id='$rand'");
if(mysql_num_rows($sql) == 0)
{
$sql = orderbyrand();
}
return $sql;
}

Untested, but that's my idea. Probably more resource intensive than order by rand() though tbh.

[DjMikeS]

Can't you use php's rand function to give you a random number...?

It allows you to specify a start number and end number...

 

http://nl.php.net/rand

[Mchl]

See the sticky 'MySQL Antipatterns' in MySQL section of the forum. Second link in the first post, slide 141 and further.

[mrMarcus]

I'm not really sure what you're doing there, but this is how you could order by rand if you don't actually want to use the mysql function. Although I'm sure it isn't resource intensive enough for anyone to actually notice...

 

function orderbyrand()
{
$sql = mysql_query("SELECT * FROM yourtable");
$count = mysql_num_rows($sql);
$rand = rand(0, $count);
$sql = mysql_query("SELECT * FROM yourtable WHERE id='$rand'");
if(mysql_num_rows($sql) == 0)
{
$sql = orderbyrand();
}
return $sql;
}

Untested, but that's my idea. Probably more resource intensive than order by rand() though tbh.

in theory, that'll work .. thing is, if say, 5 records are returned, $count is now 5.

 

but, what if the id numbers in the db are say, in the 800's.

 

this would leave something like this :

function orderbyrand()
{
   $sql = mysql_query("SELECT * FROM yourtable");
   $count = mysql_num_rows($sql); //returns 5
   $rand = rand(0, 5); //where 5 is substituted for $count
   $sql = mysql_query("SELECT * FROM yourtable WHERE id='0 or 2 or 3 or 4 or 5'");
   if(mysql_num_rows($sql) == 0)
   {
      $sql = orderbyrand();
   }
   return $sql;
}

no?

 

ultimately, what i'm trying to achieve, is to have 2 random records retrieved where something=1 .. this might be id#397 or id#3,987 in the table .. you know?

 

thanks for the help .. my brain is mush after this week.

 

i'll check it out, Mchl .. thanks.

[xtopolis]

Perhaps the other sticky "AN ORDER BY RAND() ALTERNATIVE" might offer some insight as well... ...

 

...

[corbin]

I'm not really sure what you're doing there, but this is how you could order by rand if you don't actually want to use the mysql function. Although I'm sure it isn't resource intensive enough for anyone to actually notice...

 

function orderbyrand()
{
$sql = mysql_query("SELECT * FROM yourtable");
$count = mysql_num_rows($sql);
$rand = rand(0, $count);
$sql = mysql_query("SELECT * FROM yourtable WHERE id='$rand'");
if(mysql_num_rows($sql) == 0)
{
$sql = orderbyrand();
}
return $sql;
}

Untested, but that's my idea. Probably more resource intensive than order by rand() though tbh.

 

 

ORDER BY RAND() is actually quite resource intensive in some cases.

 

 

Also, your script assumes that all IDs still exist in the database.

 

Also, COUNT() is usually better than selecting all of the rows just to find the number of rows.

[jackpf]

Yeah, that's why I've put

if(mysql_num_rows($sql) == 0)
{
$sql = orderbyrand();
}

at the end, so if it doesn't pick a record that exists, it'll repeat itself.

 

And with count(), doesn't it still have to select all records?

[Mchl]

Even if it did (which AFAIR depends on which storage engine you use), the SELECT COUNT(*) query only returns one row to PHP (instead of all the rows in a table in case of SELECT *).

[jackpf]

Hmm....I didn't know that. My code may be in for a treat today

Is it noticeably faster? As in, worth changing mysql_num_rows() to count()?

[Mchl]

Depends on how many rows are in your tables

[jackpf]

Makes sense lol.

[mrMarcus]

Perhaps the other sticky "AN ORDER BY RAND() ALTERNATIVE" might offer some insight as well... ...

 

...

i did, but it seems i get the same record returned everytime .. i just get the first record in the table returned because it's being ordered by ASC.

 

the thing is, i don't really have a clue of the size of db i'll be dealing with down the road, but i'm just trying to be prepared.