MYSQL: Query order by rand() very slow

Question

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

Answer 1

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