Random Rows From MySQL

It’s tempting to simply use MySQL’s RAND() function to select random records from a table. Consider the following query:

SELECT FLOOR(7 + (RAND() * 5));

However, when considering overhead, this is a rather expensive method to employ. This becomes glaringly apparent when dealing with large datasets.

One effective solution is to select the first and last ids, and let PHP generate the random integer (id). Then, simply check to see if the record exists using the random integer. If it exists, then add the record to an array of records.

public function getRandomRecords()
{
  // Select the first id in the target table
  $statement = $this->db->prepare("SELECT some_id
  FROM table_name
  ORDER BY some_id ASC LIMIT 1");
  $statement->execute();
  $lowest_id = $statement->fetch(PDO::FETCH_ASSOC);

  // Select the last id in the target table
  $statement = $this->db->prepare("SELECT some_id
  FROM table_name
  ORDER BY some_id DESC LIMIT 1");
  $statement->execute();
  $highest_id = $statement->fetch(PDO::FETCH_ASSOC);

  $records_array = array();

  while(true)
  {
    // Generate a random integer
    $random_id = rand( $lowest_id['some_id'], $highest_id['some_id'] );

    // Check to see if the record exists
    $statement = $this->db->prepare("SELECT col_one, col_two, etc...
    FROM table_name
    WHERE some_id = {$random_id}";
    $statement->execute();
    $result = $statement->fetchAll(PDO::FETCH_ASSOC);

    // If it exists, add it to the array
    if($result) {
      $records_array[] = $record;
    }

    $i++;

    // If the array contains 5 records, stop
    if(count($records_array) == 5) {
      break;
    }
  }

  return $records_array;
}