RAND() with big db

wazo00

I'm trying to optimize my code a bit. My db table has over 200,000 rows and I'm trying to randomly pick 20. The ORDER BY RAND() takes 2.5 seconds. I tried to make it seperate where php picks a random number and use LIMIT n, but that takes over 15 seconds. Is there any other faster way to randomly pick rows out of a pretty big table?

Thanks.

devinemke

this query takes 2.5 secs?

SELECT * FROM table ORDER BY RAND() LIMIT 20

wazo00

Yes. I've never worked with such a large db before so I'm not sure if that's normal.

wazo00

Ok, now my table has over 340,000 rows. Picking 20 random rows now takes 14-19 seconds.

Anyone have any suggestions on how to optimize the random picking?

Edit: Here's what I have so far, and it's down to around 10 seconds:

  for($a=0;$a<20;$a++) {
      $rand = mt_rand(0,347812);
      $sql = "SELECT word FROM words LIMIT ".$rand.",1";
      $res = mysql_query($sql);
      $t = mysql_fetch_row($res);
      $words[$a] = $t[0];
  }

I tried mysql_result, but it seems to be a couple seconds slower. Is what I have so far just the best way to do it?

niekos

You can ofcourse take a random number, and match it in your db.Be aware of dubbel numbers though.

But.. 2.5 sec? on my db what is heavely loaded it takes 0,5 on a 280.000 field db . Though normal select take about 0,1 so geus rand() is just awfull slow.