如果你将所有五个字段做联合索引,那进行排序时就不能使用索引了你可以使用下面的语句 来检查你的 SELECT 语句 EXPLAIN select id,title,userid,username,replyhits,readhits,lastreplytime,lastreplyid,lastreply,map,lockbbs,good,ontop from bbs where boardid='"+board+"' and master=1 and allontop=0 order by ontop desc,lastreplytime desc
手册上的资料可以解释这个了。 In some cases MySQL can uses index to satisfy an ORDER BY or GROUP BY request without doing any extra sorting. The index can also be used even if the ORDER BY doesn't match the index exactly, as long as all the unused index parts and all the extra are ORDER BY columns are constants in the WHERE clause. The following queries will use the index to resolve the ORDER BY / GROUP BY part: SELECT * FROM t1 ORDER BY key_part1,key_part2,... SELECT * FROM t1 WHERE key_part1=constant ORDER BY key_part2//楼主的情况应该是这个吧 SELECT * FROM t1 WHERE key_part1=constant GROUP BY key_part2 SELECT * FROM t1 ORDER BY key_part1 DESC,key_part2 DESC SELECT * FROM t1 WHERE key_part1=1 ORDER BY key_part1 DESC,key_part2 DESC
手册上的资料可以解释这个了。 In some cases MySQL can uses index to satisfy an ORDER BY or GROUP BY request without doing any extra sorting. The index can also be used even if the ORDER BY doesn't match the index exactly, as long as all the unused index parts and all the extra are ORDER BY columns are constants in the WHERE clause. The following queries will use the index to resolve the ORDER BY / GROUP BY part: SELECT * FROM t1 ORDER BY key_part1,key_part2,... SELECT * FROM t1 WHERE key_part1=constant ORDER BY key_part2//楼主的情况应该是这个吧 SELECT * FROM t1 WHERE key_part1=constant GROUP BY key_part2 SELECT * FROM t1 ORDER BY key_part1 DESC,key_part2 DESC SELECT * FROM t1 WHERE key_part1=1 ORDER BY key_part1 DESC,key_part2 DESC
EXPLAIN select id,title,userid,username,replyhits,readhits,lastreplytime,lastreplyid,lastreply,map,lockbbs,good,ontop from bbs where boardid='"+board+"' and master=1 and allontop=0 order by ontop desc,lastreplytime desc
key只显示一个索引名,key_len都为4,ref都为const,const,const。好像都只用了前面3个字段做索引。但在实际的速度测试中(论坛翻页):前者(5个做联合索引)明显比后者要快得多。真搞不明白。请版主赐教
In some cases MySQL can uses index to satisfy an ORDER BY or GROUP BY request without doing any extra sorting. The index can also be used even if the ORDER BY doesn't match the index exactly, as long as all the unused index parts and all the extra are ORDER BY columns are constants in the WHERE clause. The following queries will use the index to resolve the ORDER BY / GROUP BY part: SELECT * FROM t1 ORDER BY key_part1,key_part2,...
SELECT * FROM t1 WHERE key_part1=constant ORDER BY key_part2//楼主的情况应该是这个吧
SELECT * FROM t1 WHERE key_part1=constant GROUP BY key_part2
SELECT * FROM t1 ORDER BY key_part1 DESC,key_part2 DESC
SELECT * FROM t1 WHERE key_part1=1 ORDER BY key_part1 DESC,key_part2 DESC
In some cases MySQL can uses index to satisfy an ORDER BY or GROUP BY request without doing any extra sorting. The index can also be used even if the ORDER BY doesn't match the index exactly, as long as all the unused index parts and all the extra are ORDER BY columns are constants in the WHERE clause. The following queries will use the index to resolve the ORDER BY / GROUP BY part: SELECT * FROM t1 ORDER BY key_part1,key_part2,...
SELECT * FROM t1 WHERE key_part1=constant ORDER BY key_part2//楼主的情况应该是这个吧
SELECT * FROM t1 WHERE key_part1=constant GROUP BY key_part2
SELECT * FROM t1 ORDER BY key_part1 DESC,key_part2 DESC
SELECT * FROM t1 WHERE key_part1=1 ORDER BY key_part1 DESC,key_part2 DESC