MySQL select 10 random rows from 600K rows fast

Question

How can I best write a query that selects 10 rows randomly from a total of 600k?

Answer 1

You can try using the below code:

QUERY:

SELECT Name

  FROM random AS r JOIN

       (SELECT CEIL(RAND() *

                     (SELECT MAX(Id)

                        FROM random)) AS Id)

        AS r1

 WHERE r.Id>= r1.Id

 ORDER BY r.Id ASC

 LIMIT 1

Here, you can observe that the distribution of Ids is equal and that there can be gaps in the Id list as well.