MySQL select 10 random rows from 600K rows fast

mysql sql random
Francisc picture Francisc · Dec 1, 2010 · Viewed 472.5k times · Source

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

Answer

Riedsio picture Riedsio · Dec 1, 2010

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

Related questions

Simple Random Samples from a Sql database

How do I take an efficient simple random sample in SQL? The database in question is running MySQL; my table is at least 200,000 rows, and I want a simple random sample of about 10,000. The "obvious" answer is to: SELECT * FROM …

Read More
MySQL: Alternatives to ORDER BY RAND()

I've read about a few alternatives to MySQL's ORDER BY RAND() function, but most of the alternatives apply only to where on a single random result is needed. Does anyone have any idea how to optimize a query that returns …

Read More
How do I import an SQL file using the command line in MySQL?

I have a .sql file with an export from phpMyAdmin. I want to import it into a different server using the command line. I have a Windows Server 2008 R2 installation. I placed the .sql file on the C drive, and …

Read More