Fastest way get random record from DB

Question

I've been using order by rand() and it's becoming too much of a bottle neck.

I've tried this

SELECT id
FROM users
JOIN (
  SELECT CEIL( RAND( ) * ( SELECT MAX( id ) FROM users ) ) AS id
) AS r2
  USING ( id )

And it would work if all the id's were incremented, however with this particular table, they're not. The id's are fairly random themselves.

Anyone have a better method? Thanks!

Answer 1

You can use this (it has some downsides)

SELECT id FROM users ORDER BY RAND() LIMIT 0,1

Mysql reference

Answer 2

Maybe create another table and put there all id's in compact way, adding id int autoincrement? If you using this function often, new table will pay for itself.

Answer 3

well if you have a lot of rows order by rand() is strongly NOT recommended because each row needs to have the random number calculated before the resultset can be ordered.

Maybe have a look at this -> http://www.electrictoolbox.com/msyql-alternative-order-by-rand/

Answer 4

This is code I use for a quite performance-critical place in our application and it's benchmarked to perform way faster than order by rand():

$ids = mysql::getSingleColumn("select id from table where simple_where_clause = 'value'");

$limit = 3;

for ($i = 0;$i<$limit;$i++) {
    $r = rand( 0, count( $ids ) );
    if (isset($ids[$r])) {
        $usedIds[] = $ids[$r];
        unset($ids[$r]);
    } else {
        $i--;
    }
}
$idClause = implode(',',$usedIds);

I then use the $idClause in the main query: [...] WHERE id in ({$idClause})