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!