Fastest way get random record from DB

I've been using order by rand() and it's becoming to开发者_如何学Co 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!

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.

Raveren: should be faster...

$ids = mysql::getSingleColumn('query');

$limit = 3;
shuffle($ids);

for($i=0;$<$limit;$i++)
  $usedIds[] = $ids[$i];

$idClause = implode(',',$usedIds);

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/

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})

You can use this (it has some downsides)

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

Mysql reference