MySQL Random function acquisition data speed and efficiency analysis _mysql

In MySQL with a random data function, in MySQL we will have the rand () function, many friends will be directly used, if hundreds of data is certainly fine, if tens of thousands of or millions of times you will find that direct use is wrong. Let me introduce some optimization methods for random data fetching.

SELECT * FROM table_name ORDER BY RAND () LIMIT 5;

That's what Rand said in the handbook:

RAND ()
RAND (N)

Returns a random floating-point value in the range 0 through 1.0. If an integer parameter n is specified, it is used as a seed value.

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

You cannot use a column with the rand () value in an ORDER BY clause, because an order by will repeatedly compute the column multiple times. In MySQL3.23, however, you can do this: SELECT * FROM table_name ORDER BY RAND (), which is advantageous to getting a from SELECT * from Table1,table2 WHERE a=b and C<d O A random sample of the collection of Rder by RAND () LIMIT 1000. Note that a rand () in a WHERE clause will be reassessed each time the where is executed.

The net is basically querying max (ID) * RAND () to get data randomly.

SELECT * from
' table ' as T1 JOIN (select ROUND (RAND () * (SELECT MAX (ID) from ' table ') as ID) as T2
WHERE t1.id &G t;= t2.id ORDER by
T1.id ASC LIMIT 5;

But this will produce a continuous 5 records. The solution can only be one query at a time, query 5 times. Even so, because of the 150,000 table, the query only needs 0.01 seconds.

The above statement uses a join,mysql forum where someone uses

SELECT * from
' table '
WHERE ID >= (select FLOOR (MAX (ID) * RAND ()) From ' table ', ORDER by
ID LIMIT 1;

I tested it, it takes 0.5 seconds, and it's pretty good, but there's still a big gap with the statement above.

Later consulted Baidu, get the following code

The full query statement is:

SELECT * from ' table '
WHERE ID >= Select Floor (RAND () * (select MAX (ID) from ' table ')-(select MIN (id) from ' tabl E ') + (SELECT MIN (ID) from ' table ')), order by 
ID LIMIT 1;


SELECT * from ' table ' as T1 JOIN (select ROUND ("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 in PHP, the two statements are queried separately 10 times,

The former takes 0.147433 seconds.

The latter takes time 0.015130 seconds

Execution efficiency requires 0.02 sec. Unfortunately, only MySQL 4.1.* above supports such subqueries.

Note View the official manual, also said Rand () in the ORDER BY clause will be executed many times, natural efficiency and very low.

The last one of the above SQL statements, I actually test through, 100W data, instantaneous results.

Thank you for reading, I hope to help you, thank you for your support for this site!