Solutions to Improve Efficiencies of Random Select in MySQL
- 2017-10-29 15:03:04
- Memory, Renee
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 th e 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);
select * from user where id in ($ids) order by field( 'id,'.$ids) LIMIT 0, 10