3

Possible Duplicate:
Alerternative to MySQL Order By Rand()

What is an efficient way to query for random result sets in the following scenarios:

  • Select a single random row from many.
  • Select (at least) n random rows from many.
  • Select all rows in a random order.

In particular interested in MySQL, but might be a reason to try out anything else.

(Primary key is a dense AUTO_INCREMENT integer.)

Community
  • 1
  • 1
sibidiba
  • 6,270
  • 7
  • 40
  • 50

1 Answers1

2

Edit: As OMG Ponies pointed out: This doesn't scale at all. Thanks, OMG.

Try using

ORDER BY RAND()

So...

SELECT * FROM `table` ORDER BY RAND() LIMIT 1
SELECT * FROM `table` ORDER BY RAND() LIMIT n
SELECT * FROM `table` ORDER BY RAND()
jasonbar
  • 13,333
  • 4
  • 38
  • 46
  • @OMG Ponies: No, it doesn't. It is readable and does return random rows, though. I'd be interested in seeing the benchmark results (your link in the other question is dead). – jasonbar Mar 01 '10 at 04:12
  • 3
    Linky: http://www.dasprids.de/blog/2008/06/07/fetching-random-rows-of-mysql-efficiently – OMG Ponies Mar 01 '10 at 04:17
  • @jasonbar: NP, lucky it was still in my browser cache. – OMG Ponies Mar 01 '10 at 04:37
  • The catch with the approach in the link is that it assumes you are using a integer primary key. – Thomas Mar 01 '10 at 16:40
  • Btw, I realize that the OP stated that the PK was an integer, however, a better solution would be one that is generalized to not depend on the PK being a single column and of type integer. – Thomas Mar 01 '10 at 16:46