Shulou(Shulou.com)06/01 Report--

The following content mainly brings you the practical application of mysql's order by rand method. The knowledge mentioned here, which is slightly different from books, is summed up by professional and technical personnel in the process of contact with users, and has a certain value of experience sharing. I hope to bring help to the majority of readers.

Randomly returns a piece of data from a query, which is generally implemented by using the order by rand () method of mysql

For example, one user is randomly selected from 200000 users.

Mysql > select * from user order by rand () limit 1 +-- + | id | phone | password | salt | Country_code | ip | +-+ | 15160 | 6549721306 | e4f302120c006880a247b652ad0e42f2 | 40343586 | 86 | 127,127. 0.0.1 | +-+ 1 row in set (0.25 sec) mysql > explain select * from user order by rand () limit 1 +-+-- + | id | Select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +- +-+ | 1 | SIMPLE | user | ALL | NULL | 200303 | Using temporary Using filesort | +-+-- + 1 row in set (0.00 sec)

According to the results of the analysis, it takes 0.25 seconds to run, and order by rand () needs to use temporary tables (Using temporary) and file sorting (Using filesort), which is inefficient.

Improved method

1. First get the total number of records total of the query

two。 Randomly offset N entries in the total number of records (N=0~total-1)

3. Get a record using limit NMagazine 1

The code is as follows:

Analysis:

Mysql > select * from user limit 23541 +-- + | id | phone | password | salt | Country_code | ip | +-+ | 23542 | 3740507464 | c8bc1890de179538d8a49cc211859a46 | 93863419 | 86 | 127,127. 0.0.1 | +-+ 1 row in set (0.01 sec) mysql > explain select * from user limit 23541 +-+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +- -+ | 1 | SIMPLE | user | ALL | NULL | 200303 | NULL | +-- -+ 1 row in set (0.00 sec)

This article introduces the mysql order by rand () efficiency optimization method, please pay attention to more related content.

Related recommendations:

Interpreting the relevant contents of php's PDO connection database

Explain PHP object-oriented, PHP inherits related code

Use the magic method _ _ CLASS__ in PHP to get the related operations of the class name

These are the details of the actual application of mysql's order by rand method, please pay more attention to other related articles!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.