mysql order by rand() efficiency optimization method

Randomly return a piece of data from a query, generally using mysql'sorder by rand()method to achieve

For example:Randomly from 200,000 users Extract 1 user

mysql> select * from user order by rand() limit 1; +-------+------------+----------------------------------+----------+--------------+-----------+| id | phone | password | salt | country_code | ip | +-------+------------+----------------------------------+----------+--------------+-----------+| 15160 | 6549721306 | e4f302120c006880a247b652ad0e42f2 | 40343586 | 86 | 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 | NULL | NULL | NULL | 200303 | Using temporary; Using filesort | +----+-------------+-------+------+---------------+------+---------+------+--------+---------------------------------+1 row in set (0.00 sec)

According to the analysis results, the operation takes0.25 seconds, order by rand() needs to use a temporary table(Using temporary), and file sorting needs to be used（Using filesort）, low efficiency.

Improvement method

1. First get the total number of queried records total
2. Randomly offset N records in the total number of records (N=0~total-1)
3. Use limit N,1 to obtain records
The code is as follows:

2db7ab7aeda548b1a568b7abef045e28

Analysis:

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

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

Related recommendations:

Interpretation of PDO PDO connection database related content

Explanation of PHP object-oriented, PHP inheritance related Code

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

##

The above is the detailed content of mysql order by rand() efficiency optimization method. For more information, please follow other related articles on the PHP Chinese website!