Selecting random MySQL entries

Preamble

​​

Sometimes you need to select random entries from a table, for example:

• Highlight a few random blog posts and display them in the sidebar.
• Choose a random quote to display the Quote of the Day widget.
• Select a random image in the gallery and use it as a recommended image.

MySQL selects random records using ORDER BY RAND()

MySQL has no built-in statement for selecting random rows from a table. For this, you use the RAND() function.

The next query selects a random string from the database table:

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

Let’s look at the request in more detail.

• The RAND() function generates a random value for each row in the table.
• The ORDER BY clause sorts all rows in the table by the random number generated by the RAND() function.
• The LIMIT sentence selects the first row in a set of results sorted randomly.

If you want to select N random entries from the database table, you need to modify the LIMIT sentence as follows:

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

See the following table from the example database.

In the following example, five random clients are selected from the customer’s table:

SELECT
customerNumber,
customerName
FROM
customers
ORDER BY RAND()
LIMIT 5;

Note that you may get another set of results because it is random.

This technique works very well with a small table. However, it will be slow for a large table, because MySQL must sort the whole table to select the random ones.

The speed of the query also depends on the number of rows in the table. The more rows in a table, the more time it takes to generate a random number for each row.

MySQL selects random entries using the INNER JOIN sentence

This method requires that the table has a primary key field with auto increment and there is no space in the sequence.

The next query generates a random number based on the primary key column:

SELECT
ROUND(RAND() * ( SELECT MAX(id) FROM table_name)) AS id;

We can combine the table with the set of results returned by the above query as follows:

SELECT t.*
FROM table_name AS t
INNER JOIN
(SELECT ROUND(
RAND()*
(SELECT MAX(id) FROM table_NAME )) AS id
) AS x
WHERE
t.id >= x.id
LIMIT 1;

Using this technique, you have to execute the query several times to get more than one random string, because if you increase the limit, the query will only produce consecutive strings that start with a randomly selected string.

The next query will return a random client from the customer’s table.

SELECT
t.customerNumber, t.customerName
FROM
customers AS t
JOIN
(SELECT
ROUND(RAND() * (SELECT
MAX(customerNumber)
FROM
customers)) AS customerNumber
) AS x
WHERE
t.customerNumber >= x.customerNumber
LIMIT 1;

MySQL selects random records using variables

In case there is an id column in the table with values that fall into the range, 1…N and there is no skip in the range, you can use the following technique:

• First, select random numbers in the range of 1…N.
• Second, select records based on random numbers.

The following statement will help you do it:

SELECT
table. *
FROM
(SELECT
ROUND(RAND() * (SELECT
MAX(id)
FROM
table)) random_num,
@num:=@num + 1
FROM
(SELECT @num:=0) AS a, table
LIMIT N) AS b,
table AS t
WHERE
b.random_num = t.id;

Note that user variables depend on the connection. This means that this technique cannot be used with a connection pool. Besides, the primary key must be an integer and its values must be in a sequence without spaces.

 How to display random entries from a database with Mysql

Enteros

About Enteros

IT organizations routinely spend days and weeks troubleshooting production database performance issues across multitudes of critical business systems. Fast and reliable resolution of database performance problems by Enteros enables businesses to generate and save millions of direct revenue, minimize waste of employees’ productivity, reduce the number of licenses, servers, and cloud resources and maximize the productivity of the application, database, and IT operations teams.