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