提高mysql随机查询的效率

1. 基本使用:

   1	SELECT * FROM `table` ORDER BY RAND() LIMIT 0,10;

   但是在ORDER BY后面用到RAND(),查询速度非常慢.

2. MAX()*RAND()提高效率

   1 2 3

   SELECT * FROM `table` WHERE id >= (SELECT floor( RAND() * ((SELECT MAX(id) FROM `table`)-(SELECT MIN(id) FROM `table`)) + (SELECT MIN(id) FROM `table`))) ORDER BY id LIMIT 1;

3. JOIN

   1 2 3 4

   SELECT * FROM `table` AS t1 JOIN (SELECT ROUND(RAND() * ((SELECT MAX(id) FROM `table`)-(SELECT MIN(id) FROM `table`))+(SELECT MIN(id) FROM `table`)) AS id) AS t2 WHERE t1.id >= t2.id ORDER BY t1.id LIMIT 1;

• 详细介绍请参照:http://www.phpq.net/viewnews-69.html
• 转载自 http://www.ruofeel.cn/

Related Posts

• March 23, 2007 -- MySQL error connections
• January 2, 2007 -- Apache 2.2.3 PHP 5.2.0 Mysql 4.1 Install
• October 16, 2006 -- Error establishing a database connection
• September 21, 2006 -- MySQL函数SUBSTRING_INDEX
• January 18, 2012 -- MySQL regex_replace Function
• July 14, 2011 -- 十大流行PHP开发框架介绍
• June 9, 2011 -- 一天总结
• April 26, 2011 -- Yii CJSON
• March 31, 2011 -- PHP pcntl_fork
• March 11, 2011 -- Caching WordPress with Varnish