I was having having nightmares with mysql rand() function. Rand() function works well in pulling out rows randomly from the datbase and works well if you have a quite few hundred records. Ironically, it desperately slows down when you have a large table dataset above 10,000 rows.
This is the traditional way of using mysql rand() function.
SELECT * FROM TABLE ORDER BY RAND()
I have observed the execution time crossing more than 1 sec with mysql when you use this function on very large datasets.
Here is a good work around.. Instead of making mysql generate random, you generate a random value with php and then pull that random value from the table. Make sure you have ID in your table as auto-incremented one. You may need to seed the rand() function, which i have not used below.
Method 1:
$rand = rand($min, $max);
mysql_query("SELECT * FROM TABLE WHERE id='$rand' ");
Be wary that your table might have non-existing IDs, because of DELETE operations done on the table. To get around this make sure that you check whether that $rand exists as ID in the table.
Alternatively you can also use this way if you want to use WHERE query…
Method 2
Generate a random no from php and pull a record from the table which is either smaller or lesser than the random value. You may need to run this query multiple times because if you increase the limit more than 1 then it will result in sequential ones.
$rand = rand($min,$max);
mysql_query("SELECT * FROM TABLE WHERE AND id > '$rand' LIMIT 1");
There is still another way…
Method 3:
Use the OFFSET feature in the LIMIT clause where you can start at OFFSET point. This is good only for picking one random value. You will need to run this query multiple times if you want to pick more values. Make sure you find the total records from mysql_num_rows before executing this.
$result_total = mysql_query("SELECT * FROM TABLE ");
$total = mysql_num_rows($result_total);
$rand = rand(1,$total);
$result = mysql_query("SELECT * FROM TABLE WHERE LIMIT '$rand',1 ");
Although this method is good compared to using rand() function, i have found little performance issues with this method.
