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

This article introduces the relevant knowledge of "the efficiency problem and solution of querying random number of items in mysql database". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

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.

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.

This is the end of the content of "the efficiency problem and solution of querying random number of items in mysql database". Thank you for reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

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.