How Does MySQL\'s ORDER BY RAND() Function Actually Work?

How MySQL's ORDER BY RAND() Function Operates

MySQL's ORDER BY RAND() function generates seemingly random results, but its actual mechanism differs from the commonly held belief. Contrary to assumptions, MySQL does not add a random values column that influences sorting. Instead, it employs the following process:

1. Generating a Random Number: The function generates a random number between 0 and 1 for each row in the table.
2. Sorting Rows: The rows are then sorted in ascending order based on the generated random numbers.
3. Retrieving Data: When LIMIT is used with ORDER BY RAND(), MySQL retrieves rows from the top of the sorted result set.

Unexpected Execution Times

The provided test queries demonstrate unexpected execution times:

Query	Execution Time
SELECT * FROM table ORDER BY RAND() LIMIT 1	30-40 seconds
SELECT id FROM table ORDER BY RAND() LIMIT 1	0.25 seconds
SELECT id, username FROM table ORDER BY RAND() LIMIT 1	90 seconds

This variation in execution time is attributed to the different data retrieved by each query. Selecting the entire row (*) incurs a higher cost compared to retrieving only specific columns (id) or fetching data that is already indexed (id).

Alternative Methods for Fast Random Selection

While ORDER BY RAND() may not offer optimal performance, alternative methods can provide faster results:

• Jay's Method: Using a subquery to generate a random ID for row selection is efficient but can become cumbersome when dealing with complex queries.
• Procedure-Based Approach: Implementing a procedure that iterates through random IDs until finding a valid row is an effective solution but may encounter issues with large gaps in the data.

The above is the detailed content of How Does MySQL\'s ORDER BY RAND() Function Actually Work?. For more information, please follow other related articles on the PHP Chinese website!