0

Possible Duplicate:
How to request a random row in SQL?

Because of a huge table I cannot use MySQL function RAND() and trying to avoid using RAND() this way:

$a = mysql_fetch_array(mysql_query("SELECT MIN(id), MAX(id) FROM table WHERE category_id='".$var."'"));
$id = rand($a[0],$a[1]);
$id2 = rand($a[0],$a[1]);

Which doesn't work, because: in $a I have the biggest and smallest ID with the respective category_id - that's ok.

The problem brings the rows with $id and $id2 - because there are used the minimal and maximal ID values, but PHP function rand() will take some random value between minimum and maximum - and there is a big probability, that the picked out record will not belongs into category_id.

So I would like to ask you about a way, how could I fix this example.

THank you

Community
  • 1
  • 1
user984621
  • 46,344
  • 73
  • 224
  • 412
  • 1
    But the answer in that question was to use `ORDER BY RAND()`, which he says is not a workable solution for him. – Barmar Sep 21 '12 at 18:22
  • @Barmar, read the accepted answer to the end. `ORDER BY RAND()` is mentioned only to say that there is a better way, which works for any table whose size is known. – alexis Sep 24 '12 at 16:32

1 Answers1

3

You could try this:

$row = mysql_fetch_assoc(
    mysql_query(
      "SELECT count(*) as count FROM table WHERE category_id='$var'"));

$randomrow = rand(0, $row['count'] -1);

$q = mysql_query("
  SELECT * FROM table 
  WHERE category_id='$var'
  LIMIT 1 OFFSET $randomrow");
Bojan Dević
  • 1,875
  • 14
  • 24
JvdBerg
  • 21,777
  • 8
  • 38
  • 55
  • 1
    [Why does RAND() cause MySQL to overload?](http://stackoverflow.com/questions/2009060/why-does-added-rand-cause-mysql-to-overload) - if you order by something that has to be computed for each row, you will need to spend time to compute it and memory to store it. – DCoder Sep 21 '12 at 18:11
  • Thanks, did not know that. Adjusted my answer :) – JvdBerg Sep 21 '12 at 18:14