Mysql achieve random query _ MySQL

Source: Internet
Author: User
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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.