mysql order by rand() efficiency optimization method-Mysql Tutorial-php.cn

mysql order by rand() efficiency optimization method

jacklove

Release： 2018-06-08 23:36:50

Original

2143 people have browsed it

Randomly return a piece of data from a query, generally using mysql's order 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)

Copy after login

According to the analysis results, the operation takes 0.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:

<?php// 获取总记录数$sqlstr = &#39;select count(*) as recount from user&#39;;$query = mysql_query($sqlstr) or die(mysql_error());$stat = mysql_fetch_assoc($query);$total = $stat[&#39;recount&#39;];// 随机偏移$offset = mt_rand(0, $total-1);// 偏移后查询$sqlstr = &#39;select * from user limit &#39;.$offset.&#39;,1&#39;;$query = mysql_query($sqlstr) or die(mysql_error());$result = mysql_fetch_assoc($query);
print_r($result);?>

Copy after login

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)

Copy after login

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!