FRIHOST • FORUMS • FAQ • TOS • BLOGS • DIRECTORY
You are invited to Log in or Register a Frihost Account!

Querying random rows . Optimization!?!?

   Frihost Forum Index -> Scripting -> Php and MySQL
 


animassacre
I know this way
Code:
SELECT column1,column2  FROM table ORDER BY RAND() LIMIT 5


but it looks to me that this is a little bit slow and the biger my table becomes the slower it will.
It's look inlogicle to first sort the hole table randomly and then select 5 entres, there shouth be a way to just get some random rows (if there is not - it's something to be done for the next version of the server Smile ).


Question Isn't there a better way???
shamil
Code:
<?
   $result = mysql_query($query);
   $min=1;
   $max = mysql_num_rows($result);
   if($max > 0) {
       mysql_data_seek($result,rand($min,$max)-1);
       $row=mysql_fetch_assoc($result);
       //..........
    }
?>
animassacre
shamil wrote:
Code:
<?
   $result = mysql_query($query);
   $min=1;
   $max = mysql_num_rows($result);
   if($max > 0) {
       mysql_data_seek($result,rand($min,$max)-1);
       $row=mysql_fetch_assoc($result);
       //..........
    }
?>

COOL, that is definetly faster (something like 3times even more for biger tables then mine)



that is a good direction to think about.
Rolling Eyes i like the idea ,but it is still not perfect Cool
First: again you are interacting with the hole data
And 2nd: your code can return one and the same row twise or more for ex. if i wont 10 diferent rows (not very likely ,but it is something that shoud not happen)
shamil
1. Not handing whole data. It fetches only selected random rows.
2. Yes true same rows can be fetched. If u don't want u can check it.

Code:
<?
   $result = mysql_query($query);
   $min=1;
   $max = mysql_num_rows($result);
   $ar = array();
   if($max > 0) {
      $rand = rand($min,$max);
      while(in_array($rand,$ar) === true) {
         $rand = rand($min,$max);
      }
       mysql_data_seek($result,$rand-1);
       $row=mysql_fetch_assoc($result);
       //..........
    }
?>
animassacre
10x your code is great .
I would have thought of something for the check ,but it woudn't be so tidy (maybe because i didn't knew for the existence of in_array Embarassed ).
powers1983
If each row in your table has an id as the primary key then you could get unique random numbers before the query and then select only those rows from MySQL:
Code:

SELECT column1,column2  FROM table WHERE id=$rand1 OR id=$rand2


That way you are not returning the whole table in the query which may work out faster?
animassacre
powers1983 wrote:
If each row in your table has an id as the primary key then you could get unique random numbers before the query and then select only those rows from MySQL:
Code:

SELECT column1,column2  FROM table WHERE id=$rand1 OR id=$rand2


That way you are not returning the whole table in the query which may work out faster?


Smile nice try Laughing
but i don't think someone manages to keep consistent ascend column of numbers.
my ids are something like this : 2,3,5,8,9,11,12,13,14....(it's normal to delete something once in a while)
Reply to topic    Frihost Forum Index -> Scripting -> Php and MySQL

FRIHOST HOME | FAQ | TOS | ABOUT US | CONTACT US | SITE MAP
© 2005-2007 Frihost, forums powered by phpBB.