How to use Primary Key for Random Selection In MySQL
If the table has a numeric primary key with relatively few gaps, you can use it for more efficient random selection.
Syntax:
SELECT * FROM Table Name
WHERE column_name >= (SELECT FLOOR(MAX(column_name) * RAND()) FROM Employee)
ORDER BY column_name
LIMIT 10;
Example: Select Primary key for Employee Table
Query:
SELECT * FROM Employee
WHERE empId >= (SELECT FLOOR(MAX(empId) * RAND()) FROM Employee)
ORDER BY empId
LIMIT 10;
This query randomly selects a starting point based on the primary key and retrieves the next 10 rows.
Output:
Using Primary Key
Explanation: This SQL query selects 10 random rows from the Employee table by setting a minimum empId value equal to a randomly generated value between 0 and the maximum empId in the table. It then orders the selected rows by empId.
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