MYSQL randomly queried the MySQL Order By Rand of efficiency problem
- 2020-05-12 06:19:04
To extract a random record from the tablename table, write it like you would: SELECT * FROM tablename ORDER BY RAND() LIMIT 1.
However, later I checked the official MYSQL manual, and the hint for RAND() in the manual basically meant that the RAND() function could not be used in ORDER BY clause, because it would cause the data column to be scanned multiple times. However, in version 3.23 of MYSQL, randomization is still possible through ORDER BY RAND().
But the real test 1 found that this was very inefficient. A library of more than 150,000 pieces of data, query 5 pieces of data, it takes more than 8 seconds. Looking at the official manual, it also says that rand() is executed multiple times in ORDER BY clause, which is naturally inefficient and inefficient.
You cannot use a column with RAND() values in an ORDER BY clause, because ORDER BY
would evaluate the column multiple times.
Search for Google, the Internet is basically a random search for max(id) * rand() data.
SELECT * FROM `table` AS t1 JOIN (SELECT ROUND(RAND() * (SELECT MAX(id) FROM `table`)) AS id) AS t2 WHERE t1.id >= t2.id ORDER BY t1.id ASC LIMIT 5;
But this will result in five consecutive records. The solution is to query one item at a time, five times. Even that is worth it, because with 150,000 tables, the query takes less than 0.01 seconds.
The following statement is used on JOIN, which is used on mysql's forums
SELECT * FROM `table` WHERE id >= (SELECT FLOOR( MAX(id) * RAND()) FROM `table` ) ORDER BY id LIMIT 1;
Add MIN(id) to the sentence. At the beginning of the test, I did not add MIN(id), and the result was that the first few rows in the table were always queried half of the time.
The full query statement is:
SELECT * FROM `table` 2 WHERE id >= (SELECT floor( RAND() * ((SELECT MAX(id) FROM `table`)-(SELECT MIN(id) FROM `table`)) + (SELECT MIN(id) FROM `table`))) 3 ORDER BY id LIMIT 1;
FROM `table` AS t1 JOIN (SELECT ROUND(RAND() * ((SELECT MAX(id) FROM `table`)-(SELECT MIN(id) FROM `table`))+(SELECT MIN(id) FROM `table`)) AS id) AS t2
WHERE t1.id >= t2.id
ORDER BY t1.id LIMIT 1;
Finally, the two statements were queried 10 times respectively in php.
The former takes 0.147433 seconds
The latter takes 0.015130 seconds
It seems that using JOIN's syntax is much more efficient than using functions directly in WHERE.