CODEWITHSUNDEEP
databasemysql
dzm
dzm

Reputation: 23544

Fastest way get random record from DB

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!

Upvotes: 1

Views: 599

Answers (5)

Cristi
Cristi

Reputation: 11

Raveren: should be faster...

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

$limit = 3;
shuffle($ids);

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

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

Upvotes: 1

raveren
raveren

Reputation: 18541

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

Upvotes: 0

Christophe
Christophe

Reputation: 4828

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/

Upvotes: 0

alxx
alxx

Reputation: 9897

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.

Upvotes: 1

Tim
Tim

Reputation: 9489

You can use this (it has some downsides)

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

Mysql reference

Upvotes: -1

Related Questions