MySQL random fetch record order by Rand optimization

Next I will use the users (Userid,username,password ...). Table (with Ibedovan Records) as an example, to explain the next several methods of efficiency problems:

The code is as follows	Copy Code
1.select * from the Users Order by rand () LIMIT 1

Execute the SQL statement, no response for a while, finally forced to manually stop execution, how to hurt people ah! Then I looked up the MySQL manual, and the hint for rand () probably meant that the rand () function could not be used in the ORDER BY clause because it would cause the data columns to be scanned multiple times, resulting in a fairly low efficiency! The efficiency is not good, avoid use!

The code is as follows	Copy Code
2.SELECT * from the users as T1 JOIN (select ROUND (userId) from ' users ')-(select MIN (userId) from user s) + (SELECT MIN (userId) from users)) as UserId) as T2 WHERE T1.userid >= t2.userid ORDER by T1.userid LIMIT 1

Execute the SQL statement, when 0.031s, efficiency is not said, quite to the force! In the heart that cool, then, I changed "LIMIT 1" to "LIMIT 100" randomly take 100 records, spents 0.048, give force. But at this point the problem arises, and it seems that the results are not random? In order to verify the results, and executed n times, really is not random, the problem appears in the "Order by T1.userid" here, sorted by UserId. Random to take a record or a good choice, more than the AH!

The code is as follows	Copy Code
3.SELECT * from the Users WHERE userId >= (select MAX (userId) from the Users)-(select MIN (userId) from users) * RAND () + (SEL ECT MIN (userId) from users LIMIT 1

Execute this SQL statement, spents 0.039s, efficiency too! Then I changed the "LIMIT 1" to "LIMIT 10000", which was 0.063s. After many verification, the elder brother swear to the lamp, the result must be random!
Conclusion: Randomly take one or more records, the method is good!

4. Obtain the maximum and minimum value through SQL, then generate a random number randnum through the PHP rand, and then pass

The code is as follows	Copy Code
SELECT * from users WHERE userId >= randnum LIMIT 1

, to obtain a record efficiency should also be able to, many should not.

According to the type of record, the classification is continuous and discontinuous in two ways.
Continuous refers to the continuous storage of records, and there are fields to prove that the record is continuous, such as the self-increasing ID.
Non-continuous refers to the record is stored randomly, such as conditional query, the result is certainly not continuous.

First, continuous record optimization
The maximum ID and the minimum ID of the table are first obtained. Select Max (id), min (id) from table

1. In the program random one in the maximum ID and the minimum ID of the middle number, the query time greater than this random number is a random record.

SQL code

code is as follows	copy code
1.select * From table where ID > middle number limit length;  Select * FROM table where ID > middle number limit length; Disadvantages: If the middle number is very large, can not get The number of records required, and the randomness is not strong

The code is as follows

Copy Code

2. In the program random n maximum ID and the minimum ID of the middle number, the query is used in to obtain these number of intermediate records SQL code 1.select * FROM table where ID in (median 1, middle number 2, middle number 3) SELECT * FROM table where ID in (middle number 1, middle number 2, middle number 3) Note that if you are going to get 5 records, it is recommended to randomly 10 digits. Disadvantage: Less performance than the 1th method, but more random

Second, discontinuous record optimization

In fact, the method of discontinuous recording can be applied to continuous records.
Get the total number of records first, for example: SELECT COUNT (*) from table where GroupID = 1;
Then in the program random n is less than the number of middle number of records, then through the loop

SQL code

The code is as follows	Copy Code
1.select * FROM table where GroupID = 1 limit middle number, 1 SELECT * FROM table where GroupID = 1 limit intermediate number to obtain a record.

About optimizing cyclic SQL you can use prepare or union ALL to optimize cyclic execution

Conclusion: The method 1 efficiency is not good, should not be used; random access to a record, Method 2 is quite a nice choice, the syntax of the join is more efficient than directly in where the use of function efficiency is still higher, but Method 3 is also good; random access to multiple records