ORDER by RAND ()

Probably because of the need to study the MySQL random sample implementation. For example: the tablename table randomly extracts a record, as we generally do: SELECT * from TableName ORDER by RAND () LIMIT 1.

However, in the official manual of MySQL. The hint about rand () is that the rand () function cannot be used in an ORDER BY clause because it causes the data column to be scanned multiple times. But in the MySQL 3.23 version number. The Order by RAND () can still be implemented randomly.

But the real test to find that this efficiency is very low. A library of more than 150,000 articles, querying 5 data. More than 8 seconds. Check the official manual and say Rand () is run multiple times in the ORDER BY clause, naturally efficient and very low.

You cannot use a column with RAND () values in an ORDER BY clause, because order by would evaluate the column multiple time S.

Searching for Google, the internet is basically querying max (ID) * RAND () to get data randomly.

SELECT * from ' table ' as T1 joins (select ROUND (RAND () * (select MAX (ID) from ' table ')) as ID) as T2
WHERE t1.id >= t2.id
ORDER by T1.id ASC LIMIT 5;

However, this will produce 5 consecutive records.

The solution can only be one query at a time. Query 5 times. Even so it's worth it. Due to the table of 150,000 articles. The query needs only 0.01 seconds.

The following statements are used by someone using Join,mysql's forum

SELECT *
From ' table '
WHERE ID >= (SELECT floor (MAX (ID) * RAND ()) from ' table ')
ORDER by ID LIMIT 1;

I tested it. Need 0.5 seconds, the speed is also good, but with the above statement there is a very big gap.

There is something wrong with the total sleep.

So I rewrote the sentence a bit.

SELECT * from ' table '
WHERE ID >= (SELECT Floor (RAND () * (select MAX (ID) from ' table ')))
ORDER by ID LIMIT 1;

The next. Efficiency is improved, and the query time is only 0.01 seconds.

At last. Put the statement in good condition again. Plus the inference of min (id).

I was at the very beginning of the test. Because there is no inference of min (id), half of the time the result is always queried for the first few rows in the table.

The complete query statement is:

SELECT * from ' table '
WHERE ID >= (SELECT Floor (RAND () * ((select MAX (ID) from ' table ')-(select min (id) from ' table ') + (select min (id) FRO M ' 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 ') + (SEL ECT MIN (ID) from ' table ')) as ID) as T2
WHERE t1.id >= t2.id
ORDER by T1.id LIMIT 1;

Finally, the two statements were queried 10 times in PHP,

The former takes 0.147433 seconds

The latter takes time 0.015130 seconds

Opinion acceptance join is not a direct syntax where in use function even higher efficiency very

Copyright notice: This article Bo Master original article. Blog, not reproduced without consent.

ORDER by RAND ()