Solutions to Improve Efficiencies of Random Select in MySQL

[reneet]

Solution 1 - just Order By Rand()

SELECT * FROM user ORDER BY rand() LIMIT 10 This solution is not recommended, because the more the data is, the slower it is.

Solution 2 - Use JOIN method one by one, and do it 10 rounds.


SELECT * FROM `user` AS t1 JOIN (SELECT ROUND(RAND() * (SELECT MAX(id) FROM `user `)) AS id) AS t2 WHERE t1.id >= t2.id ORDER BY t1.id ASC LIMIT 1 The performance will be improved by times, but the I/O is big due to many select queries.


Solution 3 - Use SQL statement to random select the ID order, and then use IN to select.


Select the maximum and the minimun IDs first.

SELECT MAX(id),MIN(id) FROM user

Then generate values randomly.

$numbers = range ($min,$max);  //shuffle shufffle the array shuffle($numbers); //array_slice Choose a slice of the array which is bigger than the number we need to select, in case there is any ID that does not exist. $result = array_slice($numbers,0,20);

Then select

select * from user where id in ($ids) order by field('id,'.$ids) LIMIT 0,10

Problem solved!