Home > Database > Mysql Tutorial > body text

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

Barbara Streisand
Release: 2024-11-04 06:07:01
Original
562 people have browsed it

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;
Copy after login

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*/
Copy after login

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;
Copy after login

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!

source:php.cn
Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Latest Articles by Author
Popular Tutorials
More>
Latest Downloads
More>
Web Effects
Website Source Code
Website Materials
Front End Template