17

Possible Duplicate:
Selecting Random Rows in MySQL

I'm creating a simple web application using PHP and MySQL. In it, I need to randomly select a small set of rows from a table in a random order. How can I achieve such thing using MySQL?

Community
  • 1
  • 1
Mateus
  • 2,640
  • 5
  • 44
  • 62
  • 2
    Please search before asking questions. Searching the exact title of your question yields [this](http://stackoverflow.com/questions/1283640) and many other similar questions. – DaveRandom Jan 08 '12 at 17:32
  • [MySQL select 10 random rows from 600K rows fast](https://stackoverflow.com/q/4329396/6521116) – LF00 Jun 17 '17 at 11:10

3 Answers3

47
SELECT * FROM table ORDER BY RAND() LIMIT 10;

Edit:

Useful information about the MySQL RAND() function can be found here.

Mateus
  • 2,640
  • 5
  • 44
  • 62
Martin Gallagher
  • 4,444
  • 2
  • 28
  • 26
  • Ouch... I've got > 500M rows! That's a bit slow... – Jeff W Sep 01 '15 at 18:39
  • 2
    There are other performant options, if you have a sequential primary key, precompute a range of IDs in your language of choice and do a `SELECT ... WHERE id IN(x)` – Martin Gallagher Sep 01 '15 at 19:26
  • Query `select count(*) from users;` returns 10293453. Query `SELECT id FROM users ORDER BY RAND() LIMIT 10;` runs 5 sec. – serghei Oct 12 '15 at 08:26
  • Just found your solution. Thank you for it. It's incredible easy and exactly what I was looking for. Thanks! –  Jan 29 '17 at 10:22
  • your solution works, but it gets slow when you have over millions of rows at your table. So, I suggest to generate a random number in php and send it to sql query. – Almas May 31 '20 at 09:22
5
select * from table order by rand() limit 10

Note that order by rand() with large dataset is very slow but in your case it's not a problem.

Nicola Cossu
  • 54,599
  • 15
  • 92
  • 98
3

you could do that using RAND() function .

SELECT questine FROM tablename ORDER BY RAND() LIMIT 10

will select 10 questines at random under assumption the questine is stored under field questine

Vijeenrosh P.W
  • 359
  • 1
  • 3
  • 8