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?
-
2Please search before asking questions. Searching the exact title of your question yields this and many other similar questions.DaveRandom– DaveRandom2012-01-08 17:32:47 +00:00Commented Jan 8, 2012 at 17:32
-
MySQL select 10 random rows from 600K rows fastLF-DevJourney– LF-DevJourney2017-06-17 11:10:45 +00:00Commented Jun 17, 2017 at 11:10
Add a comment
|
3 Answers
SELECT * FROM table ORDER BY RAND() LIMIT 10;
Edit:
Useful information about the MySQL RAND() function can be found here.
5 Comments
Jeff W
Ouch... I've got > 500M rows! That's a bit slow...
Martin Gallagher
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)
serghei
Query
select count(*) from users; returns 10293453. Query SELECT id FROM users ORDER BY RAND() LIMIT 10; runs 5 sec.
Almas
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.
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.
Comments
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
