ORDERBYRAND () optimization of mysql _ MySQL

Mysql's ORDERBYRAND () optimization bitsCN.com

Mysql order by rand () optimization

We all know that the use of order by rand () in mysql is very slow when there is a large amount of data, because it will cause mysql to scan the entire table, so it will not use the index,

So order by rand () is already in the restricted area when it is used for random, so we should try to avoid using order by rand, therefore, it must be replaced or optimized by other methods.

When we have a random piece of data, consider the following:

[Php] $ DBI = new dbi2 ($ __dbserver); $ num = $ DBI-> result ('SELECT count (1) as num from article '); // obtain the total number of entries. use the database engine MyISAM $ num = $ num [0] ['num']-1; $ num = rand (1, $ num ); $ list = $ DBI-> result ('Select id FROM acticle order by viewcount desc limit ?, 1', $ num); // use limit

To avoid full table scan, a small portion of the data can be retrieved at random, which falls into a small range:

[Php] $ DBI = new dbi2 ($ __dbserver ); // Obtain 10 random strings in the range of 200. $ list = $ DBI-> result ('select id FROM (SELECT id FROM acticle order by viewcount desc limit ?) X order by rand () LIMIT? ', 200,10 );

BitsCN.com