Efficiency test analysis of MySQL random query records

Here is the main content of the article.
1.SELECT * FROM 'table' ORDER BY RAND() LIMIT 5
That's it.
But the real test 1 found that this was very inefficient. A library of more than 150,000 pieces of data, query 5 pieces of data, it takes more than 8 seconds
Looking at the official manual, it also says that rand() is executed multiple times in ORDER BY clause, which is naturally inefficient and inefficient.
You cannot use with RAND() values in BY clause, because ORDER would evaluate the column multiple times
Search for Google, the Internet is basically looking for max(id) * rand() to get data at random.
1. SELECT *
2.FROM 'table' AS t1 JOIN (SELECT ROUND(RAND() * (SELECT MAX(id) FROM 'table')) AS id) t2
3. WHERE t1 id > = t2.id
4.ORDER BY t1.
But this will result in five consecutive records. The solution is to query one item at a time, five times. Even that is worth it, because with 150,000 tables, the query takes less than 0.01 seconds.
The above statement is JOIN, which is used by MySQL on the MySQL forum
1. SELECT *
2. FROM ` table `
3. WHERE id > = (SELECT FLOOR( MAX(id) * RAND()) FROM `table` )
4.ORDER BY id LIMIT 1;
I tested 1 time, need 0.5 seconds, the speed is also good, but with the statement is still a big gap. I always feel something is wrong.
So I just rewrote 1.
1. SELECT * FROM ` table `
2. WHERE id > = (SELECT floor(RAND() * (SELECT MAX(id) FROM `table`)))
3.ORDER BY id LIMIT 1;
This time, the efficiency is improved again, the query time is only 0.01 seconds
Finally, complete the sentence 1 and add MIN(id). At the beginning of the test, because I did not add MIN(id), the result was that the first few rows in the table were always queried half of the time.
The full query statement is:
1. SELECT * FROM ` table `
2. WHERE id > = (SELECT floor( RAND() * ((SELECT MAX(id) FROM `table`)-(SELECT MIN(id) FROM `table`)) + (SELECT MIN(id) FROM `table`)))
3.ORDER BY id LIMIT 1;
4. SELECT *
5.FROM table AS t JOIN (SELECT ROUND(RAND) * (SELECT MAX(id) FROM table ')-(SELECT MIN FROM table '))+(SELECT MIN id) FROM 'table')) AS id) AS t2
6. WHERE t1 id > = t2.id
7.ORDER BY t1.
Finally, the two statements were queried 10 times respectively in php.
The former takes 0.147433 seconds
The latter takes 0.015130 seconds
It seems that using JOIN's syntax is much more efficient than using functions directly in WHERE. The above related content is the introduction of MySQL using rand to query and record the efficiency test. I hope you can get some results.