Selecting a Random Row With MySQL + PHP

You’d think it was as easy as pie to do, but there is no simple way to obtain a random row that is efficient at the same time.  If you Google for “mysql random row” you might have seen this:

SELECT * FROM `myTable` ORDER BY RAND() LIMIT 1

This looks easy, but if you do an EXPLAIN on that query, you will notice that the entire table is scanned for just one row.
That means the bigger your table is, the longer it will take to execute.
One easy way to get around this, is to split the logic into three parts.

1. Get a count of all the rows in your table
2. Find a random number between 0 and the count
3. Do your query with a LIMIT clause based on that random number

$countQuery = "SELECT count(*) count FROM `myTable`";
$countQueryResult = mysql_query($countQuery);
$count = mysql_result($countQueryResult, 0, "count");
$randRow = rand(0, $count-1);
$randomRowQuery = "SELECT * FROM `myTable` LIMIT $randRow, 1";

By doing this, even though you are doing two selects instead of one, the search time will become constant instead of linear and you will same precious time when your tables become larger.