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.

id
20210
30222
4002

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; 
id date value
4002 2015-07-12 some value
20210 2016-05-01 some value
30222 2016-06-02 some value

“MySQL Random Data Selection from Table”에 대한 한개의 댓글

댓글 남기기

이 사이트는 스팸을 줄이는 아키스밋을 사용합니다. 댓글이 어떻게 처리되는지 알아보십시오.

%d 블로거가 이것을 좋아합니다: