Optimization of random retrieve records in Mysql rand ()

In mysql, the Rand () function is used to query random data in a data table. However, the Rand () function uses tens of thousands of records quickly, if tens of millions of records may cause problems, let's introduce the usage of the Rand () function.

MySQL randomly queries a record.

The Code is as follows:	Copy code
$ SQL = "SELECT * FROM pinglun WHERE id> = (SELECT MAX (id) FROM pinglun)-(SELECT MIN (id) FROM pinglun) * RAND () + (select min (id) FROM pinglun) LIMIT 1 ″;

In this way, the write efficiency is relatively high. Do not use order by rand (). If the data volume is large, the order efficiency is very low.

Next I will introduce the optimization of random retrieve records.

Method 1:

Combined with the application layer

Select max (id) FROM table; obtain the largest id, and then generate a random 1 ~ MAX (id) number, such as PHP mt_rand (1, MAX (id), and then query the record with this random id.

Method 2:

Use the random id generated by the database and subquery Method

The Code is as follows:	Copy code
Select ceil (RAND () * (select max (id) FROM table ));

In this way, we can obtain a random id. (optimize MAX () without using select ceil (RAND () * MAX (id) FROM table). Execute the following command to greatly optimize

The Code is as follows:	Copy code
SELECT * FROM table WHERE id> = ( Select ceil (RAND ()*( Select max (id) FROM table ))) LIMIT 1

Method 3:
JOIN Method

The Code is as follows:	Copy code
SELECT * FROM 'table' AS t1 JOIN ( Select round (RAND ()*( Select max (id) FROM 'table') AS id ) AS t2 WHERE t1.id> = t2.id Order by t1.id ASC LIMIT 1