sql – MySQL select 10 random rows from 600K rows fast

sql – MySQL select 10 random rows from 600K rows fast

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

SELECT column FROM table
ORDER BY RAND()
LIMIT 10

Not the efficient solution but works

sql – MySQL select 10 random rows from 600K rows fast

Simple query that has excellent performance and works with gaps:

SELECT * FROM tbl AS t1 JOIN (SELECT id FROM tbl ORDER BY RAND() LIMIT 10) as t2 ON t1.id=t2.id

This query on a 200K table takes 0.08s and the normal version (SELECT * FROM tbl ORDER BY RAND() LIMIT 10) takes 0.35s on my machine.

This is fast because the sort phase only uses the indexed ID column. You can see this behaviour in the explain:

SELECT * FROM tbl ORDER BY RAND() LIMIT 10:
Simple

SELECT * FROM tbl AS t1 JOIN (SELECT id FROM tbl ORDER BY RAND() LIMIT 10) as t2 ON t1.id=t2.id
enter

Weighted Version: https://stackoverflow.com/a/41577458/893432

Leave a Reply

Your email address will not be published.