Fastest way to select a random row from a big MySQL table

In this article I will try to explain the “Fastest way to select a random row from a big MySQL table”. Sometimes application developer requires select a random id , rollnumber or something like that from a big MySQL table. If the MySQL table has more than 100K rows traditional SQL will consume significant execution time. But with some tricky method we can reduce execution time more than 50%.

To make this article I use the following random_data table having 500000 rows.

mysql> desc random_data;
+---------+---------+------+-----+---------+----------------+
| Field   | Type    | Null | Key | Default | Extra          |
+---------+---------+------+-----+---------+----------------+
| id      | int(11) | NO   | PRI | NULL    | auto_increment |
| column1 | int(11) | YES  |     | NULL    |                |
| column2 | int(11) | YES  |     | NULL    |                |
+---------+---------+------+-----+---------+----------------+
3 rows in set (0.01 sec)

The general procedure of selecting a random row from a MySQL table is

   
mysql> select * from random_data order by rand() limit 1;
+-------+---------+---------+
| id    | column1 | column2 |
+-------+---------+---------+
| 89214 |   24675 |   44192 |
+-------+---------+---------+
1 row in set (0.34 sec)

But if you use the following SQL the query execution time will be reduced significantly

mysql> select a.* from random_data a, (select max(id)*rand() randid  from random_data) b
    -> where a.id >= b.randid limit 1;
+--------+---------+---------+
| id     | column1 | column2 |
+--------+---------+---------+
| 308029 |   37104 |   67896 |
+--------+---------+---------+
1 row in set (0.19 sec)

It needs to be mentioned that the id column would be any numeric column and it is the main focus column to make the selection.