7

I have to pick 30 random records from a table, except that the query uses one second, and this slows mysql if the content is displayed by many users. This is the query:

SELECT relationship, COUNT(id) AS number FROM FR_user_friends GROUP BY relationship ORDER BY rand() LIMIT 30

Do you know how to speed up this query? Thank you.

If I remove rand() the query is fast. We have to find an alternative for rand()

Mario Chiuri
  • 91
  • 1
  • 6

3 Answers3

9

ORDER BY RAND() causes the engine to generate random values for all rows, so if you want to select a few rows from a large table, it gives very bad performance.

You could for example generate 30 random values in php in the range [1, maximum row-id] and select the first row with a row-id that is bigger or equal to the random value with LIMIT 1.

SQL-only ways to deal with this you find in How can i optimize MySQL's ORDER BY RAND() function? (but some are not trivial as well).

Community
  • 1
  • 1
syck
  • 2,984
  • 1
  • 13
  • 23
  • 7
    Yes, I can. ;-P But SO is not a coding service, but for help with specific questions. So I would suggest that you give it a try by yourself first. – syck Feb 26 '16 at 18:41
3

the RAND() function is too slow and consumes too much CPU. it generates a random number for every row and picks the smallest one so that's why it's so slow.

For speed up you can generate a random number in PHP and use in LIMIT clause like this:

$firstClauseLimit = rand(0,20); // create an int random number between 0 to 20

"... ... LIMIT $firstClauseLimit,1"

by this way, it will be so faster.

sajad abbasi
  • 1,988
  • 2
  • 22
  • 43
2

This query should be much faster than your

SELECT relationship, id 
FROM FR_user_friends fr1 
JOIN (
  SELECT CEIL(RAND() * ( SELECT MAX(id) FROM FR_user_friends )) AS id ) AS fr2 
  ON fr1.id >= fr2.id 
  LIMIT 0,30

I also suggest read more here: What would be faster?

chris
  • 37
  • 2
  • 11