MySQL – Stored Procedure that returns random rows from a table

[adsense id=”0514458240″ width=”468″ height=”60″]

I have seen lots of requests for ways of returning a set of random rows from a table. Based on the analysis performed by Akinas where he analyzed the performance impact of obtaining random rows using a variety of methods, their conclusion was that performing the request using two requests delivered the best overall performance.

Using this method we have come up with some code for a stored procedure which takes two parameters:

• A table name
• Number of rows to be returned

Here is the code for the stored procedure which is called sp_random(table, rows).

DROP PROCEDURE IF EXISTS sp_random;

DELIMITER $$
CREATE PROCEDURE sp_random(IN pTableName VARCHAR(64), IN pLimit SMALLINT UNSIGNED) 
NOT DETERMINISTIC
SQL SECURITY INVOKER
COMMENT 'A procedure that returns a random set of rows from a table'
BEGIN
	DECLARE lCount INT UNSIGNED;

	SET @uQuery := CONCAT('SELECT COUNT(1) INTO @uCount FROM ', pTableName);
	PREPARE stmt FROM @uQuery;
	EXECUTE stmt;

	IF @uCount <= pLimit THEN
		-- Return all values
		SET @query := CONCAT('SELECT * FROM ', pTableName);
	ELSE
		-- Return limited number of entries
		SET @offset := ROUND((@uCount - pLimit) * RAND(),0);
		SET @query := CONCAT('SELECT * FROM ', pTableName, ' LIMIT ', @offset, ',', pLimit);
	END IF;

	PREPARE stmt FROM @query;
	EXECUTE stmt;
	DEALLOCATE PREPARE stmt;
END;
$$
DELIMITER ;

Viewing this in action we have a table with countries defined and we will have a look at returning random rows:

mysql> select * from countries;
+----+-------------+
| id | country     |
+----+-------------+
|  1 | Spain       |
|  2 | Ireland     |
|  3 | Andorra     |
|  4 | Italy       |
|  5 | Belgium     |
|  6 | France      |
|  7 | Germany     |
|  8 | Netherlands |
|  9 | Luxembourg  |
| 10 | Poland      |
| 11 | Finland     |
| 12 | Sweden      |
| 13 | Norway      |
| 14 | Denmark     |
+----+-------------+
14 rows in set (0.00 sec)

mysql> call sp_random('countries',3);
+----+---------+
| id | country |
+----+---------+
| 10 | Poland  |
| 11 | Finland |
| 12 | Sweden  |
+----+---------+
3 rows in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> call sp_random('countries',3);
+----+---------+
| id | country |
+----+---------+
| 11 | Finland |
| 12 | Sweden  |
| 13 | Norway  |
+----+---------+
3 rows in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> call sp_random('countries',3);
+----+---------+
| id | country |
+----+---------+
| 12 | Sweden  |
| 13 | Norway  |
| 14 | Denmark |
+----+---------+
3 rows in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> call sp_random('countries',3);
+----+---------+
| id | country |
+----+---------+
|  3 | Andorra |
|  4 | Italy   |
|  5 | Belgium |
+----+---------+
3 rows in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> call sp_random('countries',3);
+----+---------+
| id | country |
+----+---------+
| 11 | Finland |
| 12 | Sweden  |
| 13 | Norway  |
+----+---------+
3 rows in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> call sp_random('countries',3);
+----+---------+
| id | country |
+----+---------+
|  1 | Spain   |
|  2 | Ireland |
|  3 | Andorra |
+----+---------+
3 rows in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> call sp_random('countries',3);
+----+---------+
| id | country |
+----+---------+
|  5 | Belgium |
|  6 | France  |
|  7 | Germany |
+----+---------+
3 rows in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)