Mysql database query random number of the efficiency of the project and solutions

Recently due to the need to study about MYSQL based random extraction method. For example, to randomly extract a record from the table table, we generally write: SELECT * FROM tablename ORDER BY RAND () LIMIT 1.

However, I checked the official MYSQL manual, which suggested for RAND () probably means that in the ORDER BY clause can not use the RAND () function, because it will lead to multiple scan data columns. However, in MYSQL version 3.23, it is still possible to achieve randomness by ORDER BY RAND ().

But really test only to find this efficiency is very low. A library of more than 150,000, inquiries 5 data, actually more than 8 seconds. See the official manual, also said rand () placed in the ORDER BY clause will be executed many times, natural efficiency and low.

Search Google, the Internet is basically the query max (id) * rand () to randomly access the data.
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 5;

But this will produce a continuous five records. The solution can only be one query each time, check 5 times. Even so it is worth, because 150,000 of the table, the query only takes less than 0.01 seconds.

The following statement uses a JOIN, mysql forum some people use

SELECT * FROM `table WHERE id> = (SELECT FLOOR (MAX (id) * RAND ()) FROM` table`) ORDER BY id LIMIT 1;

I tested it, it takes 0.5 seconds, the speed is not bad, but with the above statement is still a big gap. Always feel there is something wrong.

So I rewritten the statement a bit.
SELECT * FROM `table`
WHERE id> = (SELECT floor (RAND () * (SELECT MAX (id) FROM `table`)))
ORDER BY id LIMIT 1;

This time, the efficiency has increased, the query time is only 0.01 seconds

Finally, and then perfect the sentence, coupled with MIN (id) judgment. At the beginning of my test, I did not add MIN (id) judgment, the results of half the time always inquiries to the front of the table a few lines.
The complete query is:
SELECT * FROM `table`
WHERE id> = (SELECT floor (RAND () * ((SELECT MAX (id) FROM `table`) - (SELECT MIN (id) FROM` table`)) + )
ORDER BY id LIMIT 1;

SELECT *
FROM `table` AS t1 JOIN (SELECT ROUND (RAND () * ((SELECT MAX (id) FROM` table`) - (SELECT MIN (id) FROM `table`)) + `)) AS id) AS t2
WHERE t1.id> = t2.id
ORDER BY t1.id LIMIT 1;

The last two sentences in php query 10 times, respectively,
The former spends 0.147433 seconds The latter spends 0.015130 seconds It seems that using JOIN syntax is much more efficient than using functions directly in WHERE.