I had a few minutes free and I thought I'd post a little benchmark I came up with to test the performance of order by rand() versus picking a random id in php and getting just that row. Note that I'll be using PostgreSQL syntax for this. If you want to replicate it in mysql, feel free.
First we make a db table with some random values in it.
create table randid (id serial primary key, info int);
insert into randid (info) select generate_series(1,10000) order by random();
INSERT 0 10000
select * from randid limit 5;
id | info
----+------
1 | 9519
2 | 2867
3 | 1280
4 | 1091
5 | 4479
So we have 10,000 random rows. We can make it bigger or smaller easy, just recreate the table. I didn't go with bigger fields like text fields and such. The large the row to retrieve, the larger the advantage the method NOT using random() will have. Here's the two test files I wrote:
randtest1.php
<?php
$conn = pg_pconnect("dbname=smarlowe user=smarlowe host=localhost");
$query = "select * from randid order by random() limit 1";
$res = pg_query($query);
print pg_fetch_result($res,0,0)
?>
randtest2.php
<?php
$conn = pg_pconnect("dbname=smarlowe user=smarlowe host=localhost");
$min=1;
$max=1000;
$i = mt_rand($min,$max);
$query = "select * from randid where id=$i";
$res = pg_query($query);
print pg_fetch_result($res,0,0)
?>
randtest3.php<?php
$conn = pg_pconnect("dbname=smarlowe user=smarlowe host=localhost");
$min=1;
$query = "select max(id) from randid";
$res = pg_query($query);
$max = pg_fetch_result($res,0,0);
$i = mt_rand($min,$max);
$query = "select * from randid where id=$i";
$res = pg_query($query);
print pg_fetch_result($res,0,0)
?>
Note the use of persistent connections. I tested 100, 1k, 10k, 100k rows.
ab -c 1 -n 5000 randtestx.php
numrows | Script number | req/sec | k/s |
100 | 1 | 747 | 162k
100 | 2 | 842.08 | 183.24
100 | 3 | 563.35 | 122.58
1k | 1 | 271.04 | 59.25
1k | 2 | 758.10 | 165.72
1k | 3 | 662.75 | 144.88
10k | 1 | 27.01 | 5.89 *
10k | 2 | 787.89 | 173.02
10k | 3 | 640.18 | 140.58
100k | 1 | 2.83 | 0.62
100k | 2 | 780.39 | 172.00
100k | 3 | 559.55 | 123.33
Interesting that at low numbers the picking a random id is faster if you know your max ahead of time, but slower if you have to ask for it, than order by rand(). When we get to 1k entries in the table, order by rand() is falling pretty far behind. By the time you get to 10k though, order by rand() is useless performance wise.
Now if you need a whole bunch of the table then order by rand() might work well enough.
