Several efficient methods for mysql to randomly fetch data

I will take a look at the simplest and commonly used method. Next I will introduce the efficient method of mysql random data. If you need it, please refer to it.

I will take a look at the simplest and commonly used method. Next I will introduce the efficient method of mysql random data. If you need it, please refer to it.

1] ordinary method, low efficiency

The Code is as follows:
SELECT * FROM table order by rand () LIMIT 10;

[2] JOIN method:

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 10;

Then, improve the statement and add the MIN (id) judgment. At the beginning of the test, because I did not add the MIN (id) Judgment, half of the time is always the first few rows in the table.
The complete query statement is:

The Code is as follows:
SELECT * FROM 'table' WHERE id> = (SELECT floor (RAND () * (select max (id) FROM 'table')-(select min (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 ')) + (select min (id) FROM 'table') AS id) AS t2 WHERE t1.id> = t2.id Order by t1.id LIMIT 1;

The former takes 0.147433 seconds.
The latter takes 0.015130 seconds.
Query 10 documents.