How to Efficiently Select Random Rows from a MySQL Table Without ORDER BY RAND()?

Using MySQL to Select Random Rows

Question:

How to efficiently select a specified number of random rows from a large table without employing the potentially resource-intensive ORDER BY RAND() method?

Answer:

MySQL Solution:

To select X random rows from class "one" without using ORDER BY RAND():

SET @r := (SELECT FLOOR(RAND() * (SELECT COUNT(*) FROM mytable)));
SET @sql := CONCAT('SELECT * FROM mytable LIMIT 1 OFFSET ', @r);
PREPARE stmt1 FROM @sql;
EXECUTE stmt1;

This approach involves setting a random offset based on the table size and using it to fetch the desired number of rows.

PHP Solution:

If using PHP, you can implement the following steps to achieve the same result:

<?php $mysqli->begin_transaction();
$result = $mysqli->query("SELECT COUNT(*) FROM mytable");
$row = $result->fetch_row(); 
$count = $row[0]; 
$offset = mt_rand(0, $count);
$result = $mysqli->query("SELECT * FROM mytable LIMIT 1 OFFSET $offset");
...
$mysqli->commit();
?>

This code determines a random offset within the table's row count and uses it to select the specified number of random rows via a LIMIT and OFFSET query.

The above is the detailed content of How to Efficiently Select Random Rows from a MySQL Table Without ORDER BY RAND()?. For more information, please follow other related articles on the PHP Chinese website!