TechpulpQuestions
Selecting random records from a MySQL database table
I have a huge list of record in a MySQL table and would like to select 10 records randomly. How can I do that? Also randomness should be good.
I have a huge list of record in a MySQL table and would like to select 10 records randomly. How can I do that? Also randomness should be good.
about 2 years ago
You can use RAND() function in MySQL to radomize the records and then you can limit the results using LIMIT as shown below.
To select one random record out of all existing records:
SELECT * FROM table ORDER BY RAND() LIMIT 1
To select 5 random records out of all existing records:
SELECT * FROM table ORDER BY RAND() LIMIT 5
Like or Dislike:
0
about 2 years ago
Your solution requires ordering of all records in the table and the operation becomes slow if the table contains lots of records.
There is another solution which is rather faster but may not result in exactly required number of records. This solutions also requires prior knowledge of maximum number of records present in the table.
This solution makes more sense only if you require exactly one random record. Though the solution appear to return required number of records, all other records are not picked randomly except the first one.
SET @ID = FLOOR(RAND( )* 100) + 1;
SELECT * FROM `wp-users` WHERE ID >= @ID LIMIT 1
The 100 in the first statement is the number of maximum records in the table. Basic idea is to generate random number first and select a record that has ID equal or greater than the generated random number.
This is certainly inferior solution and may not result in expected results always.
Like or Dislike:
0