4 methods and performance comparison of MySQL query random data

  Random data selection from MySQL is also one of our most common hair, and the easiest way to do this is to use "ORDER by rand ()", which describes 4 methods of obtaining random data, including order by Rand (), and analyzes their pros and cons.

The following four scenarios are analyzed for their pros and cons. Scenario One:   code is as follows: SELECT * from ' table ' ORDER by RAND () LIMIT 0, 1; The problem with this approach is that it's very slow. The reason is that MySQL creates a zero 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 quickly. 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 take only the random number between the minimum and maximum IDs, and then get the ID for this number of rows. 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. The two 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:   code as follows: <?php $range _result = mysql_query ("Select MAX (' ID ') as max_id, MIN (' id ') as min_id from ' table '"); $range _row = mysql_fetch_object ($range _result); $random = Mt_rand ($range _row->min_id, $range _row->max_id); $result = mysql_query ("SELECT * from ' table ' WHERE ' id ' >= $random LIMIT 0,1"); As we have just mentioned, this method restricts each row of the table with a unique ID value. Well, what if it's not? 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 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 row, we used the rand () method of MySQL to generate a random number from 0 to 1. And then we multiply that number by the number of table records we get inverted with the count () method. Because the parameters of the limit must be int rather than float, we use floor () to process the result. FLOOR () calculates the maximum value of less than an expression. The final code is this: scenario three:   code as follows: <? php $offset _result = mysql_query ("Select FLOOR (RAND () * COUNT (*)) as ' offset ' from ' table '"); $offset _row = mysql_fetch_object ($offset _result); $offset = $offset _row->offset; $result = mysql_query ("SELECT * from ' table ' LIMIT $offset, 1"); After MySQL 4.1 We can use the subquery to merge the above two methods: Scenario four: The code is as follows: SELECT * from ' table ' WHERE ID >= (select FLOOR (MAX (ID) * RAND ()) from ' Tabl E ') Order by ID LIMIT 1; This scheme has the same weakness as scenario two, and is valid only for tables with unique ID values. Remember the reason we initially looked for alternatives to random rows, speed! So what will be the comparison of the execution time of these programs? I will not point out hardware and software configurations or give specific numbers. The approximate result is this: the slowest solution is one (we assume it takes 100% of the time). Programme II uses the 79% programme three –13% four –16% so, scheme three wins!