How does MySQL retrieve random data from a table?

Source: Internet
Author: User
Tags mysql manual
I originally wanted to use PHP to implement random queries, but it seemed that I had to perform more than two queries to retrieve multiple entries. recently, I found the following statement in the MySQL manual to complete the task SELECT * FROMtable_nameORDERBYrand () LIMIT5; rand said this in the manual: RAND () RAND (N) returns a random floating point value ranging from 0 to 1.0. If

I originally wanted to use PHP to implement random queries, but it seemed that I had to perform more than two queries to retrieve multiple entries. recently, I found the following statement in the MySQL manual to complete the task SELECT * FROM table_name ORDER BY rand () LIMIT 5; rand said this in the manual: RAND () RAND (N) returns a random floating point value from 0 to 1.0. If

I originally wanted to use PHP to implement random queries, but it seems that I have to perform more than two queries. Recently, I found the following statement in the MySQL manual to complete the task.

SELECT * FROM table_name order by rand () LIMIT 5;

Rand said this in the manual:
RAND ()
RAND (N)
Returns a random floating point value ranging from 0 to 1.0. If an integer parameter N is specified, it is used as a seed value.
Mysql> select RAND ();
-> 0.5925
Mysql> select RAND (20 );
-> 0.1811
Mysql> select RAND (20 );
-> 0.1811
Mysql> select RAND ();
-> 0.2079
Mysql> select RAND ();
-> 0.7888
You cannot use the column with the RAND () value in an order by clause, because order by will calculate the column multiple times. However, in MySQL3.23, You can do: SELECT * FROM table_name order by rand (), which is helpful for obtaining a data from select * FROM table1, table2 WHERE a = B AND c. Note that an RAND () in a WHERE clause will be re-evaluated every time the WHERE clause is executed.

But I tried it. It takes 0.08 sec to execute a-thousand-record table. It's a little slower.

Later I consulted google and got the following code:

SELECT *
FROM table_name AS r1 JOIN
(Select round (RAND ()*
(Select max (id)
FROM table_name) AS id)
AS r2
WHERE r1.id> = r2.id
Order by r1.id ASC
LIMIT 5;

The execution efficiency requires 0.02 sec. Unfortunately, only mysql. * And above support such subqueries.

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.