Optimization of MySQL random query

The most common form of MySQL random query is as follows:

1 SELECT * FROM TableName to RAND () LIMIT 1

This is explained in the PHP manual:

About selecting random rows from a MySQL table:

SELECT * FROM TableName ORDER by RAND () LIMIT 1

Works for small tables, but once the tables grow larger than 300,000 records/so this would be very slow because MySQL WI ll have to process all the entries from the table, order them randomly and then return the ' the ' ordered result , and this sorting takes a long time. Instead can do it like this (atleast if you have a auto_increment PK):

SELECT MIN (ID), MAX (ID) from TableName;

Fetch the result into $a

$id =rand ($a [0], $a [1]);

SELECT * FROM tablename WHERE id>= ' $id ' LIMIT 1.

The idea is that if you randomly read a record with order by RAND (), MySQL will be very laborious when the data table record reaches 300,000 or more. So the PHP manual gives you a way to combine PHP to:

First SELECT MIN (ID), MAX (ID) from TableName; Take the maximum minimum value in the database;

Then $id =rand ($a [0], $a [1]); produce a random number;

The last SELECT * from tablename WHERE id>= ' $id ' LIMIT 1 brings the random number generated above into the query;

It is obvious that the above is the most efficient.

If you need more than one record, loop through the query and remember to remove the duplicate record.

Other ways to check Google or Baidu.