How to randomly select rows from a large table in MySQL super fast?

Problem

How to randomly select rows quickly from a large table in MySQL?

Input

Try Hands-On: Fiddle

Create Input Table: Gist

Desired Output

Solution 1:

Using generated Random number

SELECT * FROM employees
ORDER BY RAND()
LIMIT 5;

Explanation:

SELECT * FROM employees: This part of the query selects all columns from the employees table.

ORDER BY RAND(): This clause orders the rows randomly. RAND() generates a random number between 0 and 1 for each row, effectively shuffling the rows.

LIMIT 5: This limits the result set to the first 5 rows. You can change the number to the desired number of randomly selected rows.

Solution 2:

More efficient / faster approach.

Using generated Random number and WHERE

SELECT * FROM employees
WHERE RAND() <= 0.1
ORDER BY RAND()
LIMIT5;

Explanation:

1. SELECT * FROM employees: This part of the query selects all columns from the employees table.

2. WHERE RAND() <= 0.1: This condition filters rows with a random number less than or equal to 0.1. By adjusting the value 0.1, you can control the probability of selecting rows. For example, setting it to 0.1 means there’s a 10% chance of selecting each row.

3. ORDER BY RAND(): This clause still orders the filtered rows randomly to ensure randomness within the selected subset.

4. LIMIT 5: This limits the result set to the first 5 rows from the filtered and randomly ordered subset.

Solution 3:

SELECT * FROM employees
WHERE RAND() <= (
    SELECT (5 / COUNT(*)) FROM employees
)
ORDER BY RAND()
LIMIT 5;

Explanation:

WHERE RAND() <= (…): Here, we use a subquery to calculate a random selection probability. (5 / COUNT(*)) calculates the probability based on the number of rows in the table. In this case, it selects approximately 5 rows out of the total count.

You can adjust the 5 to control the desired number of rows to select.

Recommended Courses

1. SQL for Data Science – Level 1
2. SQL for Data Science – Level 2
3. SQL for Data Science – Level 3

Recommended Tutorial

1. Introduction to SQL
2. SQL Window Functons – Made Simple and Easy
3. SQL Subquery

More SQL Questions

1. How to efficiently convert rows to columns in SQL?
2. How to transpose columns to rows in SQL?
3. How to select first row in each GROUP BY group?