On the experience of Mysql realizing random query _mysql

A random query of a data

Method One: SELECT * from ' table ' ORDER by RAND () Limit 1

Evaluation: Not recommended, very inefficient, as stated in the official documentation: the Order BY and Rand () are used to scan the table multiple times, resulting in slower speeds.

Method Two: SELECT * from ' table '
WHERE ID >= (SELECT Floor (RAND () * (select MAX (ID) from ' table '))
ORDER by ID LIMIT 1;

Explanation: SELECT max (ID) from ' table ' to query for the maximum 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 that are greater than the IDs that generate random numbers

Then the query that is larger than this random ID is queried, and then sorted by ID, select the first one, which is equivalent to getting a random row in all rows.

Evaluation: There is a problem, if the ID is not starting from 0, for example, starting from 10000, then SELECT floor (RAND () * (SELECT MAX (ID) from ' table ') will be able to get a great probability of a value less than 10000, The probability that the result of a where-qualified query will be all the results of the query is increased, and the limit 1 gets the first row of data.

Method Three: SELECT * from ' table '
WHERE ID >= (select Floor () (select MAX (ID) from ' table ') + (select MIN (ID) from ' table ') + (Selectmin (ID) from ' table '), ORDER by ID LIMIT 1;

Method IV: SELECT *
From ' table ' as T1 JOIN (select ROUND (select MAX (ID) from ' table ') (select MIN (IDs) from ' tables ')) +

(SELECT MIN (ID) from ' table ')) As ID) as T2
WHERE t1.id >= t2.id
ORDER by t1.id LIMIT 1;

Evaluation: Solves the problem of MAX (ID) in method two, RAND () * (select MAX (ID) from ' table ')-(select MIN (ID) from ' table ') + (Selectmin (ID) from ' table ') You can get random numbers in Max (id) and min (id).

Method Four is a little faster than the method three, http://www.jb51.net/article/42229.htm this article points out that 15w data takes 0.147433 seconds, while the latter takes 0.015130 seconds.

The above solution defaults to a number field that is not duplicated. In fact, many of the tables are now designed with a single increment as the primary key, of course, and some are the UUID as the primary key, and there is no number key, so you can use the MySQL function to convert the string of the UUID to numbers. And there is a problem, if the ID field of the distribution of the number of uneven (such as the distribution by 1,4,5,6,7,8,45), but also caused by random query unreasonable, but here is not discussed so complex issues.

Second, random query more than one data

Method One: Limit 1 of a random query data is modified into limit 5

Evaluation: The data obtained in this way is continuous.

Method Two:

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-as T2 on T1.id=t2.id

ORDER by t1.id LIMIT 1;

Explain:

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 takes 50 random digits, then on t1.id=t2.id picks up random data that is no more than 50 rows and then takes 5.