What is the best practice for selecting random rows in MySQL without duplicates?

When selecting random rows in MySQL without duplicates, one common approach is to use the `ORDER BY RAND()` clause in the query. However, this method can be inefficient for large datasets. A more efficient way is to first select all the primary keys or unique identifiers of the rows, shuffle them in PHP, and then use them to fetch the corresponding rows from the database. 

// Step 1: Get all primary keys or unique identifiers
$query = "SELECT id FROM your_table";
$result = mysqli_query($connection, $query);

$ids = [];
while ($row = mysqli_fetch_assoc($result)) {
    $ids[] = $row['id'];
}

// Step 2: Shuffle the array of ids
shuffle($ids);

// Step 3: Select a random subset of ids and fetch corresponding rows
$randomIds = array_slice($ids, 0, 5); // Get 5 random ids
$randomIdsString = implode(',', $randomIds);

$query = "SELECT * FROM your_table WHERE id IN ($randomIdsString)";
$result = mysqli_query($connection, $query);

while ($row = mysqli_fetch_assoc($result)) {
    // Process the fetched rows
}

Keywords

PHP MySQL random rows duplicates query optimization

Related Questions