Solutions to Improve Efficiencies of Random Select in MySQL

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);

Then select

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

Problem solved!