mysql implements random query experience talk

1. Randomly query 1 piece of data

Method 1: SELECT * FROM 'table' ORDER BY RAND() limit 1

Evaluation: it is not recommended to use, and the efficiency is very low. It is explained in the official document: Order By and RAND() are used together, and the table will be scanned many times, resulting in slow speed.

Method 2: SELECT * FROM 'table'
WHERE id > = (SELECT floor(RAND() * (SELECT MAX(id) FROM `table`)))
ORDER BY id LIMIT 1;

Explanation: SELECT MAX(id) FROM 'table' finds the maximum id value

SELECT floor(RAND() * (SELECT MAX(id) FROM 'table')

WHERE id > = (SELECT floor(RAND() * (SELECT MAX(id) FROM 'table'))

And then at the end you query for the rows that are larger than this random id, and then you sort by id, and you select the first row, which is equivalent to getting a random 1 of all the rows.

Evaluation: there is a problem, if not id starting from 0, such as from 10000 began to increase, so SELECT floor (RAND () * (SELECT MAX (id) FROM ` table `)) get yo will be a big probability is less than the value of 10000, after a where limit the query results will be all the chance to get bigger, the query result is finally limit 1 to obtain data line 1 chance to get higher.

Method 3: SELECT * FROM 'table'
WHERE id > = (SELECT floor( RAND() * ((SELECT MAX(id) FROM `table`)-(SELECT MIN(id) FROM `table`)) + (SELECTMIN(id) FROM `table`))) ORDER BY id LIMIT 1;

Method 4: 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 1;

Evaluation: The problem of MAX(id) in method 2 was solved, RAND() * (SELECT MAX(id) FROM 'table')-(SELECT MIN(id) FROM 'table') + (SELECTMIN(id) FROM 'table') can get the random Numbers in MAX(id) and MIN(id).

Methods 4 3 slightly faster than method 1 point, https: / / www ofstack. com article / 42229 htm this article pointed out that the former time of 0.147433 seconds, article 15 w data the latter time of 0.015130 seconds.

In fact, many tables are designed with 1 self-increment segment as the primary key. Of course, there are also 1 with uuid as the primary key, but no numeric key. In this way, mysql's function can be used to convert uuid's string into a number. There is also a problem that if the Numbers of the id field are not evenly distributed (such as 1,4,5,6,7,8,45), it will also make the random query unreasonable, but I will not discuss such a complex issue here.

2. Query multiple data randomly

Method 1: change the limit 1 of a random query to limit 5

Evaluation: the data obtained in this way will be continuous.

Method 2:

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

from `table` limit 50) AS t2 on t1.id=t2.id

ORDER BY t1.id LIMIT 1;

Explanation:

SELECT ROUND(RAND() * ((SELECT MAX(id) FROM `table`)-(SELECT MIN(id) FROM `table`))+(SELECT MIN(id) FROM `table`)) AS id

from 'table' limit 50) will get 50 random Numbers, and then on t1.id=t2.