Fetching multiple random rows from a database

As a follow up to my earlier article about fetching a single random row from MySQL I will tell you today, how you can fetch multiple random rows from a table without any hassle. Compared to the solution with fetching a single random row, fetching multiple random rows requires some tricks. Let us start with creating two new test tables:

CREATE TABLE `test` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` VARCHAR( 32 ) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE = InnoDB;

CREATE TABLE `test_gapless` (
  `test_id` INT UNSIGNED NOT NULL,
  `random_id` INT UNSIGNED NOT NULL,
  PRIMARY KEY (`test_id`),
  UNIQUE (`random_id`)
) ENGINE = InnoDB;

Now we have two tables. Our primary table containing the usual stuff like a primary ID and a name. Additionally we have a gapless table related to it. The tricky part here is the random_id. The idea is, that independent of deleting and adding rows, the random_id column always goes from 1 to the number of existent rows in the test table, without any holes or duplicates. To approach this, we are going to write two triggers for the main table. Let's start with the insert trigger:

DELIMITER //
CREATE TRIGGER `test_insert` AFTER INSERT ON `test`
  FOR EACH ROW BEGIN
    DECLARE `next_random_id` INT;
    
    SELECT (COALESCE(MAX(`random_id`), 0) + 1)
    INTO `next_random_id`
    FROM `test_gapless`;
    
    INSERT INTO `test_gapless` (
      `test_id`,
      `random_id`
    ) VALUES (
      NEW.`id`,
      `next_random_id`
    );
END
//
DELIMITER ;

This insert trigger will always assign the next highest random_id to the newly inserted rows. Now we have to keep track of deleted rows with another trigger:

DELIMITER //
CREATE TRIGGER `test_delete` AFTER DELETE ON `test`
  FOR EACH ROW BEGIN
    DECLARE `current_random_id` INT;
    
    SELECT `random_id`
    INTO `current_random_id`
    FROM `test_gapless`
    WHERE `test_id` = OLD.`id`;
    
    DELETE FROM `test_gapless` WHERE `test_id` = OLD.`id`;
    
    UPDATE `test_gapless`
    SET `random_id` = `random_id` - 1
    WHERE `random_id` > `current_random_id`;
END
//
DELIMITER ;

Now as we have our triggers ready, we can insert some dummy data into the table:

INSERT INTO `test` (
  `name`
) VALUES
  ('Foo'),
  ('Bar'),
  ('Baz'),
  ('PHP'),
  ('MySQL'),
  ('Zend Framework'),
  ('HTML'),
  ('JavaScript'),
  ('CSS'),
  ('AJAX'
);

Now let's take a look at the generated data:

mysql> SELECT * FROM `test` AS `t`
    -> INNER JOIN `test_gapless` AS `g`
    -> ON `g`.`test_id` = `t`.`id`;
+----+----------------+---------+-----------+
| id | name           | test_id | random_id |
+----+----------------+---------+-----------+
|  1 | Foo            |       1 |         1 | 
|  2 | Bar            |       2 |         2 | 
|  3 | Baz            |       3 |         3 | 
|  4 | PHP            |       4 |         4 | 
|  5 | MySQL          |       5 |         5 | 
|  6 | Zend Framework |       6 |         6 | 
|  7 | HTML           |       7 |         7 | 
|  8 | JavaScript     |       8 |         8 | 
|  9 | CSS            |       9 |         9 | 
| 10 | AJAX           |      10 |        10 | 
+----+----------------+---------+-----------+
10 rows in set (0.00 sec)

Everything looks fine and the random_id was always assigned correctly. Now let's try to delete a some rows from the table:

mysql> DELETE FROM `test` WHERE `id` BETWEEN 3 AND 4;

Then we check the random table again to make sure that there are no holes in the random_id:

mysql> SELECT * FROM `test` AS `t`
    -> INNER JOIN `test_gapless` AS `g`
    -> ON `g`.`test_id` = `t`.`id`;
+----+----------------+---------+-----------+
| id | name           | test_id | random_id |
+----+----------------+---------+-----------+
|  1 | Foo            |       1 |         1 | 
|  2 | Bar            |       2 |         2 | 
|  5 | MySQL          |       5 |         3 | 
|  6 | Zend Framework |       6 |         4 | 
|  7 | HTML           |       7 |         5 | 
|  8 | JavaScript     |       8 |         6 | 
|  9 | CSS            |       9 |         7 | 
| 10 | AJAX           |      10 |         8 | 
+----+----------------+---------+-----------+
8 rows in set (0.01 sec)

And as you can see, everything is still fine. We don't care about updates to the id-column for now, but that can easily be accomplished by with an update trigger or a constraint. Our next part is to use the random_id to actually select some random rows from out main table. We can do this in multiple ways, like completly in a stored procedure, but for this tutorial we will do the entire thing on client side for simplicity.

First we need to get as many random IDs as we want to fetch rows from the database Let's say we want to fetch three random rows:

<?php
$highestRandomId = /* SQL: SELECT MAX(`random_id`) FROM `test_gapless` */;
$numRows         = min(3, $highestRandomId);
$randomIds       = array();

while (count($randomIds) < $numRows) {
    $randomId = rand(1, $highestRandomId);
    
    if (!in_array($randomId, $randomIds)) {
        $randomIds[] = $randomId;
    }
}

Now we have an array filled with all random IDs we need. Now we can fetch our rows:

SELECT * FROM `test` AS `t`
INNER JOIN `test_gapless` AS `g`
ON `g`.`test_id` = `t`.`id`
WHERE `g`.`random_id` IN ($randomIds);

You will now get something like that:

+----+----------------+---------+-----------+
| id | name           | test_id | random_id |
+----+----------------+---------+-----------+
|  6 | Zend Framework |       6 |         4 | 
|  8 | JavaScript     |       8 |         6 | 
|  5 | MySQL          |       5 |         3 | 
+----+----------------+---------+-----------+

Hopefully this tutorial gave you some ideas, not only how to fetch random rows from MySQL while keeping a good performance, but also how you can trick around in MySQL to do stuff which is normally not easily possible.