Mysql achieve random query _ MySQL

Mysql implements random query of bitsCN.com

1. randomly query a piece of data

Method 1: SELECT * FROM 'table' order by rand () limit 1

Rating: it is not recommended and the efficiency is very low. it is described in the official document that Order By and RAND () can be used to scan the table multiple 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' queries the largest id value.

SELECT floor (RAND () * (select max (id) FROM 'table') returns a random number smaller than MAX (id ).

WHERE id> = (SELECT floor (RAND () * (select max (id) FROM 'table') returns all rows with IDs greater than the generated random number.

In the end, query the rows greater than this random id, sort the rows by id, and select the first one, which is equivalent to obtaining a random row in all rows.

Rating: there is a problem. if the id is not FROM 0, for example, FROM 10000, SELECT floor (RAND () * (select max (id) FROM 'table ')) the result will be a very high probability of getting a value smaller than 10000. the where-qualified query results will increase the probability of all query results, the last result obtained by limit 1 is that the probability of data in the first row increases.

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;

Comment: solved the problem of MAX (id) in method 2, RAND () * (select max (id) FROM 'table')-(select min (id) FROM 'table') + (SELECTMIN (id) FROM 'table') can obtain random numbers in MAX (id) and MIN (id.

Method 4 is a little faster than method 3, the http://blog.csdn.net/zxl315/article/details/2435368 blog pointed out that the former took 0.147433 seconds for 15 million pieces of data, the latter took 0.015130 seconds.

The above solutions all have a non-repeated numeric field by default. In fact, many tables are designed to use an auto-incrementing segment as the primary key, and some of them use uuid as the primary key, without a number key, you can use the mysql function to convert the uuid string to a number. Another problem is that, if the number distribution of the id field is uneven (such as distribution by, and 45), it will also lead to unreasonable random queries, however, we will not discuss such complicated issues here.

II. randomly query multiple data entries

Method 1: Change limit 1 of a random query to limit 5.

Comment: the obtained data 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) to obtain 50 random numbers, and thenOn t1.id = t2.id, the system selects random data of no more than 50 rows, and then obtains 5 rows.

BitsCN.com