The simplest way of selecting random rows from the MySQL database is to use "ORDER BY RAND()" clause in the query.
Solution 1--
SELECT * FROM `table` ORDER BY RAND() LIMIT 0,1;
The problem with this method is that it is very slow. The reason for it being so slow is that MySQL creates a temporary table with all the result rows and assigns each one of them a random sorting index. The results are then sorted and returned.
There are several workarounds to speed things up.
The basic idea is to get a random number and then select a specific row using this number.
In the case that all the rows have unique ids we will just have to pick a random number between the smallest and the biggest id and then select the row with id that equals that number. To make this method work when ids are not evenly distributed we will have to use ">=" operator instead of "=" in the last query.
To get the minimum and maximum id values in the entire table we will use
MAX() and
MIN() aggregate functions. These functions will return minimum and maximum value in the specified group. The group in our case is all the values of `id` column in our table.
Solution 2 [PHP]
$range_result = mysql_query( " SELECT MAX(`id`) AS max_id , MIN(`id`) AS min_id FROM `table` ");
$range_row = mysql_fetch_object( $range_result );
$random = mt_rand( $range_row->min_id , $range_row->max_id );
$result = mysql_query( " SELECT * FROM `table` WHERE `id` >= $random LIMIT 0,1 ");
As we mentioned this method is limited to tables with unique id for each row. What to do if it's not the case?
The solution is to use the MySQL LIMIT clause. LIMIT accepts two arguments. The first argument specifies the offset of the first row to return, and the second specifies the maximum number of rows to return. The offset of the initial row is 0 (not 1).
To calculate the offset to the first row we will generate a random number between 0 and 1 using MySQL's RAND() function. Then we will multiply this number by number of records in the table, which we will get using COUNT() function. Since LIMIT arguments must be integers and not float values we will round the resulting number using FLOOR() function. FLOOR() is an arithmetic function that calculates the largest integer value that is smaller than or equal to the expression. The resulting code will look like this:
