MySQL Random Data Selection from Table

In this article, introduce how to extract random data effectively from MySQL table. Many solutions in stackoverflow are very slow or inefficient.

Select Random Id

SELECT ROUND(RAND()*(SELECT COUNT(*) FROM table_name)) AS id;

At first, we can create random id which is in your table table_name.

Select Random Id List

And then, by using this,

SELECT ROUND(RAND()*(SELECT COUNT(*) FROM table_name)) AS id FROM table_name LIMIT 100;

We can get 100 random id from this SQL.

If you want to get more rows, change LIMIT count that you want.

Query results are like below.

Select Random Dataset

then join this id with original table.

This SQL is very fast even if we can get fully random data. (under 1sec per query in 1 million dataset)

SELECT t1.* FROM
    table_name AS t1
    JOIN
    (
        SELECT ROUND(RAND()*(SELECT COUNT(*) FROM table_name)) AS idx FROM table_name LIMIT 100
    ) AS t2
WHERE t1.id = t2.id ORDER BY id;