1

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 } ?>
Community
  • 1
  • 1
Mensur
  • 457
  • 9
  • 28

1 Answers1

0

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.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Your first query took: 0.2934 sec. Second query took: 0.0532 sec.. If I only right the query I wrote but which i need to run 10 times it takes for 1 time: 0.0009 sec. The thing is i need to run thiss maaaaaaaany times and I really dont' want to overload my DB. – Mensur Oct 09 '16 at 02:32
  • @Mensur . . . But the first query is a single query and basically won't take longer as you increase the number being returned. – Gordon Linoff Oct 09 '16 at 02:34
  • My concern is when generation sitemap.xml where I have on my page displayed all my users from table 10K+, then you are able to click on that user and see more info about the user, but also when viewing user page on that page 10 other users will be randomly generated again. So my question is, generating sitemap.xml file will it then make the site overload ? – Mensur Oct 09 '16 at 02:38
  • @Mensur . . . Once you show data on a page, you should not be "randomly generating" the same data again. Perhaps I misunderstand the comment. – Gordon Linoff Oct 09 '16 at 02:40