We all know that order by rand () Optimization in mysql is slow when there is a large amount of data, because it can cause full table scanning in mysql, therefore, no index is used. Therefore, order by rand () is already in the restricted area when used for random queries. Therefore, 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 );