How to Select Random Records in MySQL

Sometimes you may need to pick random rows from your database table, for the purpose of inspection or displaying on your website. For example, you may want to show random blog posts or images on your website. In MySQL, there is no built-in function to select random records. In this article, we will look at how to select random records in MySQL.

How to Select Random Records in MySQL

Here are the steps to select random records in MySQL. Let us say you have the following table.

mysql> select * from users;
+-------------+---------+
| date_joined | user_id |
+-------------+---------+
| 2020-04-28  |     213 |
| 2020-04-28  |     214 |
| 2020-04-30  |     215 |
| 2020-04-28  |     216 |
| 2020-04-28  |     217 |
| 2020-04-30  |     218 |
| 2020-04-28  |     219 |
| 2020-04-28  |     220 |
| 2020-04-30  |     221 |
| 2020-05-01  |     222 |
| 2020-05-01  |     222 |
| 2020-05-01  |     223 |
| 2020-05-04  |     224 |
| 2020-05-04  |     225 |
| 2020-05-04  |     226 |
| 2020-05-04  |     226 |
+-------------+---------+

Here is the general SQL query to select n random records in MySQL.

SELECT * FROM table_name
ORDER BY RAND()
LIMIT n;

Here is the SQL query to get 3 random records in MySQL.

select * from users order by rand() limit 3;
+-------------+---------+
| date_joined | user_id |
+-------------+---------+
| 2020-05-01  |     222 |
| 2020-04-28  |     216 |
| 2020-05-05  |     230 |
+-------------+---------+

Please note, you may get a different result, since it is a random result after all.

Let us look at the above SQL query in detail.

In the above query, RAND() function generates random value for each row in table. ORDER BY sorts the rows based on this random number generated for each row. LIMIT clause filters the result to the number of rows you want.

The above query works well with small tables but it will slow down as the number of rows increase. This is because MySQL has to sort the entire table before picking the random rows.

If you are working with large tables, then you can try the following query which uses Inner Join. But it requires you to have a primary key column. For our example, it is user_id column.

SELECT r1.*
  FROM users AS r1 JOIN
       (SELECT (RAND() *
                     (SELECT MAX(user_id)
                        FROM users)) AS user_id)
        AS r2
 WHERE r1.user_id >= r2.user_id
 ORDER BY r1.user_id ASC
 LIMIT 3;
+-------------+---------+
| date_joined | user_id |
+-------------+---------+
| 2020-05-01  |     222 |
| 2020-04-28  |     216 |
| 2020-05-05  |     230 |
+-------------+---------+

In the above query, we use the following subquery that generates random number from max primary key value.

SELECT (RAND() *
       (SELECT MAX(user_id)
       FROM users)) AS user_id

Then we do an Inner Join between the original table and the result of above subquery to get a table of random rows. Finally, we use LIMIT clause to filter only required random rows.

How to Select Random Records in MySQL

How to Select Random Records in MySQL

Related posts:

Leave a Reply Cancel reply