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:

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:

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:

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

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

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

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

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:

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

You will now get something like that:

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.