Randomly get one or more records of MySQL data table

There are a number of ways to randomly get one or more records of a MySQL data sheet, and I'll use the users (Userid,username,password ...). ) Table (with Alligators records) as an example, compare the following several methods of efficiency issues:

2. SELECT * from the Users Order by rand () LIMIT 1
   Execute the SQL statement, long time no response, and finally forced to stop the execution manually, how a hurt people ah! Then I checked the MySQL manual, and the Hints for rand () probably mean that the rand () function cannot be used in an ORDER by clause because it causes the data column to be scanned multiple times, resulting in a fairly low efficiency! The efficiency is not good, avoid use!
4. SELECT * from users as T1 joins (select ROUND (RAND () * ((select MAX (userid) from ' users ')-(select MIN (userid) from users) ) + (SELECT MIN (userid) from users) as UserId) as T2 WHERE T1.userid >= t2.userid ORDER by T1.userid LIMIT 1
   Execute the SQL statement, time 0.031s, efficiency didn't see?, quite to the force! The heart that cool Ah, immediately thereafter, I changed the "Limit 1" to "limit 100" randomly take 100 records, Time 0.048, to force it. But at this point the problem arises and the results seem to be not random? In order to verify the results and execute n times, it is not random, the problem appears in "Order by T1.userid" here, sorted by UserId. Random take a record is a good choice, many will not be AH!
6. SELECT * from the Users WHERE userId >= ((select MAX (userid) from the Users)-(select MIN (userid) from users)) * RAND () + (Selec T MIN (userId) from users) LIMIT 1
   Execute the SQL statement, time 0.039s, efficiency is too force! Then I changed "Limit 1" to "limit 10000", Time 0.063s. After many tests, brother to the lamp swear, the result is certainly random!
   Conclusion: Random take one or more records, the method is good!
8. The maximum and minimum values are obtained through SQL, and then a random number randnum is generated from the PHP rand, and then the SELECT * from users WHERE userId >= randnum LIMIT 1, the efficiency should be similar,

   Conclusion: Method 1 is not efficient, do not use; randomly get a record, Method 2 is a pretty good choice, using the syntax of join is more efficient than directly in where, but Method 3 is good; randomly get multiple records, Method 3 didn't see?!

Reprint: http://blog.csdn.net/nieyanlong/article/details/7204475

Randomly get one or more records of MySQL data table