Performance optimization of random data from tables in MySQL

The simplest way.

Rand () Function instance

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

Take time for 0.7888

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