Generate random MySQL rows quickly and run same sql query multiple times

Question

I have found an example where it generates a random row quickly: MySQL select 10 random rows from 600K rows fast

Now I would like to run that query 10 times but I'm getting exactly same output instead of different rows. Any ideas how to solve this:

Here is my code:

<?php
    for ($e = 0; $e <= 14; $e++) {
         $sql_users = "SELECT user_name, user_age, country, age_from, age_to, gender, profile_image, gender_search, kind_of_relationship
                          FROM users AS r1 JOIN
                               (SELECT CEIL(RAND() *
                                             (SELECT MAX(id)
                                                FROM users)) AS id)
                                AS r2
                         WHERE r1.id >= r2.id
                         ORDER BY r1.id ASC
                         LIMIT 1";
         $statement6 = $dbConn->prepare($sql_users);
         $statement6->execute();
         more = $statement6->fetch(PDO::FETCH_BOTH);
?>

    <?php echo $more['user_name'];?>

<?php } ?>

Gordon Linoff Gordon Linoff · Accepted Answer · 2016-10-09T02:27:56

If you want ten rows, how bad is the performance of:

select u.*
from users u
order by rand()
limit 10;

This does do exactly what you want. And, getting all the rows in a single query saves lots of overhead in running multiple queries. So, despite the order by rand(), it might be faster than your approach. However, that depends on the number of users.

You can also do something like this:

select u.*
from users u cross join
     (select count(*) as cnt from users u) x
where rand() < (10*5 / cnt)
order by rand()
limit 10;

The where clause randomly chooses about 50 rows -- give or take. But with a high confidence, there will be at least 10. This number sorts quickly and you can randomly choose 10 of them.

Generate random MySQL rows quickly and run same sql query multiple times

1 Answers