Mysql random row from large database table faster

May-23-2014 in Mysql, PHP No comments

The question of “HOW TO SELECT RANDOM ROWS IN MYSQL?”

To select mysql random row from large database, you can refer to the following steps:

Using ORDER BY RAND()

The simplest way of selecting random rows from the MySQL database is to use “ORDER BY RAND()” clause in the query database.

The following code:

SELECT * FROM random_table ORDER BY RAND() LIMIT 1

1	SELECT * FROM random_table ORDER BY RAND() LIMIT 1

But there is only one problem with this solution – it is very slow, as RAND() should be executed for each row, assigned and only after this MySQL will be able to select the record. If table is small this approach will work and probably will work best. But what if table contains 100 000+ records? or even more…because mysql needs to create temporary table. So never do ti this way

Using MySQL with PHP code to select random row

The following code:

<?php

$query = "SELECT FLOOR(RAND() * COUNT(*)) AS random_row FROM random_table";

$result = mysql_query($query);

$row = mysql_fetch_array($result);

$query = "SELECT * FROM random_table LIMIT ".$row['random_row'].", 1";
?>