MySQL randomly extracts data SQL statements from tables

That's what Rand said in the handbook:

RAND ()
RAND (N)

Cases

The code is as follows	Copy Code
SELECT * FROM table_name ORDER BY RAND () LIMIT 5;

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.

The code is as follows	Copy Code
Mysql> select RAND (); -> 0.5925 Mysql> Select RAND (20); -> 0.1811 Mysql> Select RAND (20); -> 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 obtaining a random sample of a collection from the SELECT * from Table1,table2 WHERE a=b and C This. Note that a rand () in a WHERE clause will be reassessed each time the where is executed.

Remove Random Data performance optimization

From the above data I feel that the data is small certainly no problem big certainly not, I am a 5W record data table Random read will be 10 seconds, too slow.

If it's going to hang when the volume is large, then find a way.

The code is as follows	Copy Code
SELECT * FROM table_name as R1 JOIN (select ROUND (RAND () * (SELECT MAX (ID) from table_name) as ID) as R2 WHERE r1.id >= r2.id ORDER by r1.id ASC LIMIT 5;

Spend time 0.02 sec

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

The code is as follows	Copy Code
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 the speed is good, but there is still a big gap with the above statement. There is something wrong with the total sleep.

So I rewrote the statement.

The code is as follows	Copy Code
SELECT * from ' table ' WHERE ID >= (SELECT Floor (RAND () * (select MAX (ID) from ' table ')) ORDER by ID LIMIT 1;

This, the efficiency is increased, the query time is only 0.01 seconds

Finally, the statement to improve, plus the min (id) judgment. I was at the beginning of the test, because I did not add min (id) judgment, the result has

Half of the time is always queried to the previous rows in the table.
The full query statement is:

The code is as follows	Copy Code
SELECT * from ' table ' WHERE ID >= (select floor (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 () (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