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!

possible duplicate of [How to request a random row in SQL?](http://stackoverflow.com/questions/19412/how-to-request-a-random-row-in-sql) — Peter Lang, Sep 02 '10 at 07:58

score 1 · Accepted Answer · answered Sep 02 '10 at 07:56

1

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.

answered Sep 02 '10 at 07:56

alxx

9,897
4
26
41

That's not a bad idea actually, so then I can use the improved query I posted but do it based off the autoincrement column - Good idea! Will try that. – dzm Sep 02 '10 at 19:54
Don't forget to add trigger to update compact table when original table is updated. – alxx Sep 03 '10 at 06:27

score 1 · Answer 2 · answered Mar 17 '11 at 12:32

1

Raveren: should be faster...

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

$limit = 3;
shuffle($ids);

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

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

answered Mar 17 '11 at 12:32

Cristi

11
1

score 0 · Answer 3 · answered Sep 02 '10 at 07:57

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/

score 0 · Answer 4 · answered Sep 02 '10 at 08:05

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

score -1 · Answer 5 · answered Sep 02 '10 at 07:55

-1

You can use this (it has some downsides)

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

Mysql reference

answered Sep 02 '10 at 07:55

Tim

9,351
1
32
48

1

Wasn't that exactly what Dave tries to avoid for performance reasons? – Peter Lang Sep 02 '10 at 07:57
also watch this post http://stackoverflow.com/questions/2663710/how-does-mysqls-order-by-rand-work – Tim Sep 02 '10 at 07:57

Fastest way get random record from DB

5 Answers5

Linked