Mysql random query record set and mysql query record

Mysql random query record set and mysql query record

Sometimes, you need to randomly query several record sets from the database. After searching online, several blog posts are repeated ..... I don't know who copied them. Apart from introducing a method that I think of myself, I will use mysql's rand () in essence ()

Method 1:

SELECT * FROM reportcard_patient_temp order by rand () LIMIT 10

This is the easiest way to think of using the mysql random function order by. I checked the execution plan in mysql 5.6.27 by explaining:

+ ---- + ------------- + ------------------------- + ------ + ------------- + ------ + --------- + ------ + --------------------------------- +
| Id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+ ---- + ------------- + ------------------------- + ------ + ------------- + ------ + --------- + ------ + --------------------------------- +
| 1 | SIMPLE | reportcard_patient_temp | ALL | NULL | 2479 | Using temporary; Using filesort |
+ ---- + ------------- + ------------------------- + ------ + ------------- + ------ + --------- + ------ + --------------------------------- +
1 row in set

It can be seen that the select type is simple (here it is a single table). It is acceptable to randomly retrieve 10 records of nearly 0.4 million of the data, and it takes only about 1.2 seconds, as I said on the Internet, there are no repeated queries.

 

Second

SELECT * FROM 'table' WHERE id> = (select floor (MAX (id) * RAND () FROM 'table') order by id LIMIT 10;

The biggest drawback of this method is that what if the primary key is not auto-incrementing? Of course, you can still use the auto-increment primary key.

 

Third, this is the method I have come up with. Since we need to use the random functions that come with mysql, how to convert rand () into a non-auto-incrementing primary key becomes the key to the problem, use the count (*) Integer Set

SELECT * FROM (
SELECT zyid, ROUND (COUNT (*) * RAND () * 100) AS newno FROM yw_syjgb group by zyid
) AS t order by t. newno asc LIMIT 10

For the same 0.4 million record set, it takes about 0.2 seconds to randomly retrieve 10 records. It can be said that the increase is quite large than that of order by rand, of course, you can also use ROUND (COUNT (*) * RAND () * 100) AS newno AS the value after limit to randomly locate the record set, note that the number of records must be greater than 100. If the number is smaller than 100, it is okay to divide the number by 10 or 100.

 

There are still many fragmented methods based on random queries at the database layer. Here I think there are still many methods that meet the needs of our own businesses. For example, you can make random selection algorithms in programs, or implement a rand () function by yourself.