Here’s a quick way to return random rows from a table in MariaDB.

Suppose we have a table called Pets with the following data:

SELECT  
    PetId,
    PetName
FROM Pets;

Result:

+-------+---------+
| PetId | PetName |
+-------+---------+
|     1 | Fluffy  |
|     2 | Fetch   |
|     3 | Scratch |
|     4 | Wag     |
|     5 | Tweet   |
|     6 | Fluffy  |
|     7 | Bark    |
|     8 | Meow    |
+-------+---------+

We can use the RAND() function in conjunction with an ORDER BY clause and the LIMIT keyword to return random rows from that table.

Example:

SELECT  
    PetId,
    PetName
FROM Pets 
ORDER BY RAND() 
LIMIT 5;

Example result:

+-------+---------+
| PetId | PetName |
+-------+---------+
|     5 | Tweet   |
|     7 | Bark    |
|     1 | Fluffy  |
|     8 | Meow    |
|     3 | Scratch |
+-------+---------+

And if I run it again, I get this:

+-------+---------+
| PetId | PetName |
+-------+---------+
|     3 | Scratch |
|     8 | Meow    |
|     4 | Wag     |
|     7 | Bark    |
|     6 | Fluffy  |
+-------+---------+

And so on…

Bear in mind that this is quite intensive, and should not be used on larger tables.

When using this technique, MariaDB reads all rows in the table, generates a random value for each of them, orders them, and finally applies the LIMIT clause. This will result in a very slow query on large tables.

See Data Sampling: Techniques for Efficiently Finding a Random Row on the MariaDB website for techniques more suited to larger tables.