Efficiently Selecting Random Rows From a MySQL Table

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 select the record using the random integer.

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;
}