从600K 行中随机选择10行，速度很快

Question

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

转载于:https://stackoverflow.com/questions/4329396/mysql-select-10-random-rows-from-600k-rows-fast

Answer 1

A great post handling several cases, from simple, to gaps, to non-uniform with gaps.

http://jan.kneschke.de/projects/mysql/order-by-rand/

For most general case, here is how you do it:

SELECT name
  FROM random AS r1 JOIN
       (SELECT CEIL(RAND() *
                     (SELECT MAX(id)
                        FROM random)) AS id)
        AS r2
 WHERE r1.id >= r2.id
 ORDER BY r1.id ASC
 LIMIT 1

This supposes that the distribution of ids is equal, and that there can be gaps in the id list. See the article for more advanced examples