Optimization of mysql random Query

The most common method for mysql random query is as follows:

1 SELECT * FROM tablename order by rand () LIMIT 1

The php manual explains this:

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 or so this will be very slow because MySQL will have to process ALL the entries from the table, order them randomly and then return the first row of the ordered result, and this sorting takes long time. instead you can do it like this (atleast if you have an auto_increment PK ):

Select min (id), MAX (id) FROM tablename;

Fetch the result into $

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

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

The general idea is that if you use order by rand () to randomly read records, mysql will be very difficult when the number of data table records reaches 0.3 million or more. therefore, the php Manual provides a method that can be implemented in combination with php:

First select min (id), MAX (id) FROM tablename; obtain the maximum and minimum values in the database;

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

Finally, SELECT * FROM tablename WHERE id> = '$ id' LIMIT 1 to bring the random number generated above into the query;

Obviously, the above is the most efficient.

If multiple records are required, query them cyclically and remember to remove duplicate records.

For other methods, refer to google or Baidu.