MySQL randomly fetches one or more data

Original address: http://www.im286.com/thread-7091552-1-1.html

Go back to Backup

The study of some stochastic factors is mainly about the efficiency problem.

Statement one:

Select  from 1

The instructions in the MySQL Manual 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, inefficient, and no use.

---------Divider Line------------------------------------

Statement two:

    SELECT * from users as  T1  1

Executing the SQL statement takes 0.031s and is very efficient. When "Limit 1" is changed to "limit 100" randomly take 100 records, Time 0.048s. But at this point the problem arises and the results seem to be not random. In order to verify the result, it was executed n times, which is not random. The problem appears in "Order by T1.userid" here, sorted by UserId. Random to take a record is a good choice, many will not die AH.

---------Divider Line------------------------------------

Statement three:

SELECT * from the Users WHERE userId >= ((select MAX (userid) from the Users)-(select MIN (userid) from users)) * RAND () + (Selec T MIN (userId) from users)  1

Executing the SQL statement, when 0.039s, is also very efficient. Then the "Limit 1" was changed to "limit 10000", Time 0.063s. After many tests, the results are random.

---------Divider Line------------------------------------

Conclusion: The statement one efficiency not, should not use. Get a random record, statement Two is a pretty good choice, using the syntax of join is more efficient than directly in where to use the function or higher. Statement Three is also good, random access to more than one record of choice.

MySQL randomly fetches one or more data