END IF;
RETURN @levels;
END
在网页显示的时候用了类似下面的SQL语句。
mysql> SELECT u.*, count_level(u.id) FROM feed_urls u ORDER BY ref_count DESC LIMIT 12000,1G
*************************** 1. row ***************************
id: e42f44b04dabbb9789ccb4709278e881c54c28a3
link: http://tetellita.blogspot/
title: le hamburger et le croissant
feed_url: .blogger/feeds/7360650/posts/default
update_time: 2012-05-15 14:50:53
state: ok
http_server: GSE
abstract: Lepekmezest un épais sirop bordeaux obtenu par réduction dumoût de raisin, une sorte de mélasse de raisin, en somme. Légèrement acidulé, il apporte du pep's aux yaourts et nappe avec bonheur les
previous_id: 129cabd96e7099a53b78c7ddeff98658351082e9
ref_count: 9
error: NULL
aid: 174262
count_level(u.id): 8
1 row in set (4.10 sec)
好吧，悲剧了!4100ms。一定对12000个条目都算了一次count_level，然后再进行排序。所以才用上了4秒那么漫长的时间!!!
改进方法：
先SELECT LIMIT，再在派生的临时表里，计算count_level。
mysql> SELECT u.*, count_level(u.id) FROM (
SELECT id FROM feed_urls ORDER BY ref_count DESC LIMIT 27521,1
) d JOIN feed_urls u ON u.id=d.idG
*************************** 1. row ***************************
id: 61df288dda131ffd6125452d20ad0648f38abafd
link: http://mynokiamobile/
title: My Nokia Mobile
feed_url: http://mynokiamobile/feed/
update_time: 2012-05-14 14:06:57
state: ok
http_server: Apache/2.2.19 (Unix) mod_ssl/2.2.19 OpenSSL/1.0.0-fips mod_auth_passthrough/2.1 mod_bwlimited/1.4 FrontPage/5.0.2.2635
abstract: ArchivesSelect MonthMay 2012April 2012March 2012February 2012January 2012December 2011November 2011October 2011September 2011August 2011July 2011June 2011May 2011April 2011March 2011February 2011Janua
previous_id: f37af92bb89c08f6d4b69e72eab05d8ab1e2aca4
ref_count: 5
error: NULL
aid: 154996
count_level(u.id): 8
1 row in set (0.09 sec)
如此，优化之后效果好很多了!但是还可以继续优化，例如建立一个字段存储Level的值应该是最好的办法了。
初次了解MySQL一些工作机制，欢迎一起探讨!
参考文献：
http://explainextended/2009/10/23/mysql-order-by-limit-performance-late-row-lookups/
.mysqlperformanceblog/2006/09/01/order-by-limit-performance-optimization/