How to get the highest efficiency of random data acquisition by MySQL

Xiaobian to share with you how MySQL random access to data the highest efficiency, I believe most people do not know how, so share this article for your reference, I hope you read this article after a great harvest, let us go to understand it together!

Found in the SQL statement there is an ORDER BY rand() such a statement, this is said to be convenient to use, but the efficiency is too low, so I used the following method to optimize, is to use the JOIN table method to achieve this random data row method, you can use EXPLAIN SQL statement to analyze the efficiency of the following two SQL statements, of course, the amount of data at least 100,000 or more to see the performance.

[1]Common methods are too inefficient

SELECT * FROM table ORDER BY rand() LIMIT 10;

[2]The JOIN method:

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 t2

WHERE t1.id >= t2.id

ORDER BY t1.id LIMIT 10;

The above is "MySQL how to randomly get the most efficient" all the content of this article, thank you for reading! I believe that everyone has a certain understanding, hope to share the content to help everyone, if you still want to learn more knowledge, welcome to pay attention to the industry information channel!