• notice
  • Congratulations on the launch of the Sought Tech site

Mysql implementation of random query experience

1.Query a piece of data randomly

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

Evaluation: It is not recommended, 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 multiple times, resulting in slower 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` This sentence queries the largest id value

               SELECT floor(RAND() * (SELECT MAX(id) FROM `table`)) This sentence gets a random number less than MAX(id)

WHERE id >=(SELECT floor(RAND() * (SELECT MAX(id) FROM `table`))) This sentence filters out all rows with id greater than the random number generated

    Then finally the rows larger than this random id are queried, and then sorted by id, choosing the first one is equivalent to obtaining a random row of all the rows.

         Evaluation: There is a problem, if the id does not start from 0, such as self-increasing from 10000, then SELECT floor(RAND() * (SELECT MAX (id) FROM `table`)) will get a high probability of getting a value less than 10000.The query result limited by where will have a greater chance of all query results.Finally, limit 1 gets the first The probability of row data becomes 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: Solve the problem of MAX(id) in Method 2, RAND() * ((SELECT MAX(id) FROM `table`)-(SELECT MIN(id) FROM `table`)) + (SELECTMIN(id) FROM `table`) can get random numbers in MAX(id) and MIN(id).

Method 4 is slightly faster than Method 3.http://www..net/article/42229.htm This article points out that 15w pieces of data takes 0.147433 seconds for the former and 0.015130 seconds for the latter.

The above solutions all have a unique numeric field by default.In fact, many tables are designed with an auto-increment segment as the primary key.Of course, some use uuid as the primary key, but no numeric key.In this case , You can use the function of mysql to convert the string of uuid into a number.And there is another problem.If the numbers in the id field are not evenly distributed (for example, according to 1,4,5,6,7,8,45), it will also cause random queries to be unreasonable, but it will not be discussed here.The problem is complicated.

Second, random query of multiple data

Method 1: Change the limit 1 of a random data 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) This will get 50 random numbers, and then on t1.id=t2.id will pick out random data with no more than 50 rows, and then just take 5.

Tags

Technical otaku

Sought technology together

Related Topic

0 Comments

Leave a Reply

+