MySQL random function to obtain data speed and efficiency analysis, mysql Random Function

MySQL random function to obtain data speed and efficiency analysis, mysql Random Function

The random data fetch function is included in mysql. in mysql, we will have the rand () function, which many friends will use directly. If there are hundreds of data entries, it will certainly be okay, if tens of thousands or millions of users, you will find that direct use is incorrect. Next I will introduce some Optimization Methods for random data retrieval.

SELECT * FROM table_name order by rand () LIMIT 5;

Rand said this in the manual:

RAND ()
RAND (N)

Returns a random floating point value ranging from 0 to 1.0. If an integer parameter N is specified, it is used as a seed value.

mysql> select RAND();    -> 0.5925mysql> select RAND(20);    -> 0.1811mysql> select RAND(20);    -> 0.1811mysql> select RAND();    -> 0.2079mysql> select RAND();    -> 0.7888

You cannot use the column with the RAND () value in an order by clause, because order by will calculate the column multiple times. However, in MySQL3.23, You can do: SELECT * FROM table_name order by rand (), which is helpful for obtaining a data from select * FROM table1, table2 WHERE a = B AND c <d ORDER BY RAND () LIMIT 1000 random samples of the set. Note that an RAND () in a WHERE clause will be re-evaluated every time the WHERE clause is executed.

Basically, data is randomly obtained by querying max (id) * rand () on the Internet.

SELECT *FROM `table` AS t1 JOIN (SELECT ROUND(RAND() * (SELECT MAX(id) FROM `table`)) AS id) AS t2WHERE t1.id >= t2.idORDER BY t1.id ASC LIMIT 5;

However, five consecutive records are generated. The solution is to query only one item at a time and query five times. Even so, it is worthwhile because it takes less than 0.15 million seconds to query 0.01 tables.

The preceding statement uses JOIN, Which is used on the mysql forum.

SELECT *FROM `table`WHERE id >= (SELECT FLOOR( MAX(id) * RAND()) FROM `table` )ORDER BY id LIMIT 1;

I tested it. It took 0.5 seconds and the speed was good, but there was still a big gap with the above statements.

Later I consulted baidu and got the following code:

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 t2WHERE t1.id >= t2.idORDER BY t1.id LIMIT 1;

Finally, these two statements are queried 10 times in php respectively,

The former takes 0.147433 seconds.

The latter takes 0.015130 seconds.

The execution efficiency requires 0.02 sec. Unfortunately, only mysql. * And above support such subqueries.

Please refer to the official manual for notes. It also means that rand () will be executed multiple times in the order by clause, which is naturally inefficient and inefficient.

The last one of the preceding SQL statements passes the actual test, with million data records and results instantly.

Thank you for reading this article. I hope it will help you. Thank you for your support for this site!