Extract 50 random records from a Mysql database table (large dataset)

Question

I need to get some random entries from a table. The table contains about 100,000 records.

Records do not have to be consistent, and performance is important.

I tried " order by rand()", but the performance is bad ( ~ 3 seconds)

Answer 1

You can try to do this in the php loop, but I doubt it will be faster ..

$iMaxID = getMaxIdFromYourTable(); //not real php
$records = array();
while (true) {
    $iRandID = rand(1,$iMaxID);
    thisRecord = "SELECT FROM yourtable WHERE id = $iRandID";
    if (numrows > 0) {
        $records[] = thisRecord;
        if (count($records) > 50) {
            break;
        }
    }
}

Answer 2

I just ran a simple one SELECT * FROM table ORDER BY RAND() LIMIT 50;on the table with 229,291 lines. Completed in 0.63 seconds. Given that RAND () is really slow and there should be a better solution.

, ORDER BY RAND(). : http://wanderr.com/jay/order-by-slow/2008/01/30/

. , , , , PHP-, SELECT * FROM table WHERE id IN(5,3,1);.

, : ORDER BY RAND() MySQL?

, 50 (0,09 ) 229 291 .

Answer 3

SET @o = (SELECT FLOOR(RAND() * COUNT(*)) FROM your_table);
PREPARE STMT FROM 'SELECT * FROM your_table LIMIT ?, 1';
EXECUTE STMT USING @o;