Randomly pick data from MySQL

--Randomly select data from MySQL

-------------------------2014/06/23

The simplest way to randomly select data from MySQL is to use "ORDER by RAND ()";

Programme one:

[PHP] view plaincopy 

1. SELECT * from ' table ' ORDER by RAND () LIMIT 0, 1;

The problem with this approach is that it is very slow. The reason is because MySQL creates a zero-day table to hold all the result sets, then gives each result a random index, and then sorts and returns.

There are several ways to get it up.

The basic idea is to get a random number and then use that random number to get the specified row.

Since all rows have a unique ID, we will only take the random number between the minimum and maximum IDs, and then get the ID for this number of lines. In order for this method to be effective when the ID is discontinuous, we use ">=" instead of "=" in the final query.

To get the minimum and maximum IDs for the entire table, we use the Max () and Min () two aggregate functions. Both methods return the maximum and minimum values in the specified group. Here This group is the value of all the ID fields in our table.

Scenario Two:

[PHP] view plaincopy 

1. $range _result = mysql_query ( "Select MAX (' ID ') as max_id, MIN (' id ') as min_id from ' table ');
2. $range _row = mysql_fetch_object ( $range _result);
3. $random = Mt_rand ( $range _row->min_id, $range _row->max_id);
4. $result = mysql_query ( "SELECT * from ' table ' WHERE ' id ' >= $random LIMIT 0,1");

As we just mentioned, this method restricts each row of the table with a unique ID value. So what if it's not the case?

The following scenario uses the MySQL limit clause. Limit receives two parameter values. The first parameter specifies the offset of the first row of the returned result, and the second parameter specifies the maximum number of rows to return the result. The offset specifies that the first row is 0 instead of 1.

To calculate the offset of the first line, we use the rand () method of MySQL to generate a random number from 0 to 1. Then we multiply this number by the number of table records we get by using the count () method. Since the parameter of limit must be of type int and cannot be float, we use floor () to process the result. Floor () calculates the maximum value that is less than the expression. The final code is this:

Programme III:

[PHP] view plaincopy 

1. $offset _result = mysql_query ( "Select Floor (RAND () * COUNT (*)) as ' offset ' from ' table '");
2. $offset _row = mysql_fetch_object ( $offset _result);
3. $offset = $offset _row->offset;
4. $result = mysql_query ( "SELECT * from ' table ' LIMIT $offset, 1");

After MySQL 4.1 We can use sub-subqueries to merge the above two methods:

Programme IV:

[PHP] view plaincopy 

1. SELECT * from ' table ' WHERE ID >= (select Floor (MAX (ID) * RAND ()) from ' table ') the ORDER by ID of LIMIT 1;

This scenario has the same weaknesses as scenario two, only valid for tables with unique ID values.

Remember the reason that we initially looked for alternative ways to select random lines, speed! So what happens to the execution time comparisons of these scenarios? I will not point out hardware and software configurations or give specific numbers. The approximate result is this:

• The slowest is the solution one (we assume it takes 100% of the time).
• Scenario two uses 79%.
• Scenario Three-13%
• Scenario Four-16%

Plan three wins!