Optimization of random query value efficiency in mysql

In mysql, random data query is a commonly used function, but if this random query function is not used well, your database will be stuck, especially to a few hundred thousand, more attention should be paid to the case of millions of data. The following describes how to optimize the random query value efficiency in mysql.

In mysql, random data query is a commonly used function, but if this random query function is not used well, your database will be stuck, especially to a few hundred thousand, more attention should be paid to the case of millions of data. The following describes how to optimize the random query value efficiency in mysql.

Mysql uses rand () for random queries

The Code is as follows:
1 order by rand () limit x

The random mysql query efficiency is extremely low. I met several authors of wordpress plug-ins tonight. The random values are all direct.

The Code is as follows:
1 order by rand ()

This is too boring. There is a lot of data, for example, you have a 50 thousand ~ 0.1 million, with thousands of IP addresses each day, the efficiency is similar to snail bait. Try it. This is not serious

Responsible for random queries.

Later, Baidu found a method.

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

But it still has no effect. Let's look at the following:

The Code is as follows:
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

This is much faster, but this method will cause most of the values to be within the range before 1/2 and need to be modified again:

The Code is as follows:
SELECT * FROM 'table' AS t1 JOIN (select round (RAND () * (select max (id) FROM 'table')-(select min (id) FROM 'Table') + (select min (id) FROM 'table') AS id) AS t2 WHERE t1.id> = t2.id Order by t1.id LIMIT 1;