Shulou(Shulou.com)05/31 Report--

This article mainly introduces how to optimize the efficiency of Order By Rand () in MySQL, which has a certain reference value, interested friends can refer to, I hope you can learn a lot after reading this article, let the editor take you to understand it.

Recently, I have studied the implementation of random extraction of MYSQL due to the need. For example, to extract a record randomly from the tablename table, the general way to write it is: SELECT * FROM tablename ORDER BY RAND () LIMIT 1.

Later, however, I checked MYSQL's official manual, and the hint for RAND () probably meant that the RAND () function could not be used in the ORDER BY clause because it would cause the data column to be scanned multiple times. However, in MYSQL 3.23, random can still be achieved through ORDER BY RAND ().

But after a real test, it was found that the efficiency was very low. A library of more than 150000 items, query 5 pieces of data, unexpectedly need more than 8 seconds. Looking at the official manual, it is also said that rand () will be executed many times in the ORDER BY clause, which is naturally efficient 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 Google, the Internet is basically to query max (id) * rand () to get data at random.

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 can only be queried one at a time, 5 times. Even so, it is worth it, because of the 150000 tables, the query takes less than 0.01s.

The following statement is used by someone on JOIN,mysql

SELECT *

FROM `table`

WHERE id > = (SELECT FLOOR (MAX (id) * RAND ()) FROM `table`)

ORDER BY id LIMIT 1

I tested it, and it took 0.5 seconds, and the speed was good, but there was still a big gap with the above statement. I always feel that there is something wrong.

So I rewrote the sentence.

SELECT * FROM `table`

WHERE id > = (SELECT floor (RAND () * (SELECT MAX (id) FROM `table`)

ORDER BY id LIMIT 1

In this case, the efficiency has been improved again, and the query time is only 0.01 seconds.

Finally, refine the sentence and add the judgment of MIN (id). At the beginning of the test, because I didn't add the MIN (id) judgment, I always queried the first few rows in the table half the time.

The complete query statement is:

SELECT * FROM `table`

WHERE id > = (SELECT floor (RAND () * ((SELECT MAX (id) FROM `table`)-(SELECT MIN (id) FROM `table`) + (SELECT MIN (id) FROM `table`)

ORDER BY id LIMIT 1

SELECT *

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 are queried 10 times respectively in the.

The former takes 0.147433 seconds.

The latter takes 0.015130 seconds.

It seems that using the syntax of JOIN is much more efficient than using functions directly in WHERE.

Thank you for reading this article carefully. I hope the article "how to optimize Order By Rand () efficiency in MySQL" shared by the editor will be helpful to you. At the same time, I also hope you will support us and pay attention to the industry information channel. More related knowledge is waiting for you to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.