Random Sampling with User Variables
For more control over randomness, especially in large datasets, we can use user variables to assign a random number to each row and then select based on these numbers.
Syntax:
SET @row_number = 0;
SELECT *
FROM (
SELECT *, (@row_number:=@row_number + 1) AS num
FROM Table Name
ORDER BY RAND()
) AS t
WHERE num % (SELECT ROUND(COUNT(*) / 10) FROM Table Name) = 0
LIMIT 10;
Example: Select Random Sampling for Employee Table
Query:
SET @row_number = 0;
SELECT *
FROM (
SELECT *, (@row_number:=@row_number + 1) AS num
FROM Employee
ORDER BY RAND()
) AS t
WHERE num % (SELECT ROUND(COUNT(*) / 10) FROM Employee) = 0
LIMIT 10;
This query first assigns a row number to each row in a random order, then selects rows based on these numbers.
Output:
Using Random Sampling
Explanation: This SQL query assigns sequential row numbers to each row in the Employee table, orders them randomly, and then selects every 10th row based on the total row count, resulting in a set of 10 random rows.
How to Select 10 Random Rows from 600K Rows Fast in MySQL?
Selecting random rows simultaneously from a database is a common task in SQL especially when handling large datasets. Selecting multiple rows is useful for sampling data or generating random subsets for analysis. In MySQL, this can be achieved using various methods, each has its advantages.
In this article, we explore retrieving 10 random rows from a dataset of 600k rows in MySQL using three methods. We present their syntax, examples, and explanations, providing insights into efficient random row selection.
Contact Us