Retrieve 50 random records from a Mysql database t

Question

I need to retrieve a number of random records from a table. The table contains about 100000 records.

The records must not be consecutive and performance is important.

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

Answer 1

You could try to do it in a 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 SELECT * FROM table ORDER BY RAND() LIMIT 50; on a table with 229,291 rows. Completed in 0.63 seconds. Given, that RAND() is really slow and there should be a better solution.

I am only aware of various alternate way to select just one random row from a large data set, faster than using ORDER BY RAND(). which is explained here: http://wanderr.com/jay/order-by-slow/2008/01/30/

For multiple random rows, I am currently unaware of a better solution. If the ID's are subsequent and no id's are missing in between, you could generate a comma separated list of random numbers in your own PHP code - and then using a SELECT * FROM table WHERE id IN(5,3,1); query.

Here is a working solution, in another Stack Overflow question: How can i optimize MySQL's ORDER BY RAND() function?

Worked for me, 50 rows in set (0.09 sec) for the table with 229,291 records.

Answer 3

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