How Does ORDER BY RAND() Work in MySQL, and Why Is It So Slow?

Understanding ORDER BY RAND() in MySQL

While the ORDER BY RAND() syntax seems straightforward, its internal workings can be enigmatic. This article delves into the mechanics behind this operation and addresses some unexpected performance discrepancies.

How ORDER BY RAND() Functions

Initially, it was believed that ORDER BY RAND() added a column of random values to the table and sorted based on that. However, subsequent research revealed that Jay's proposed method is significantly faster:

SELECT * FROM Table T JOIN (SELECT CEIL(MAX(ID)*RAND()) AS ID FROM Table) AS x ON T.ID >= x.ID LIMIT 1;

This method generates a random ID within the range of existing IDs and retrieves the first matching row.

Performance Variations

Interestingly, the performance of ORDER BY RAND() varies based on the columns included in the result:

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 discrepancy can be attributed to indexing. The id column is indexed, making its retrieval fast. Adding username to the result requires reading it from each row, slowing down the operation. In the case of *, all columns, including variable-length ones, need to be loaded into memory, further impacting performance.

Alternatives for Fast Random Selection

For efficient random row selection, consider the following alternatives:

• The procedure described in the German blogger's archive (web.archive.org/web/20200211210404/http://www.roberthartung.de/mysql-order-by-rand-a-case-study-of-alternatives/).
• Use Jay's method, despite its increased complexity, for optimal speed.
• Employ the following two-query approach for simplicity:

SELECT id FROM table ORDER BY RAND() LIMIT 1;
SELECT * FROM table WHERE id=ID_FROM_PREVIOUS_QUERY LIMIT 1;

The above is the detailed content of How Does ORDER BY RAND() Work in MySQL, and Why Is It So Slow?. For more information, please follow other related articles on the PHP Chinese website!