SQL 加了Order By超慢,去掉飞快,怎么解决

[sksgod]

SELECT
	novel_booklist.ID AS aID,
	novel_chapter.ID AS bID,
	novel_category.ID AS cID,
	novel_booklist.Author,
	novel_booklist.Category,
	novel_chapter.BookName,
	novel_chapter.ChapterName,
	novel_chapter.ID,
	novel_chapter.UpTime
FROM
	novel_chapter,
	novel_booklist,
	novel_category
WHERE
	novel_chapter.BookName = novel_booklist.BookName
AND novel_booklist.Category = novel_category.CategoryName
ORDER BY
	novel_chapter.UpTime DESC
LIMIT 20

加了Order By超慢,去掉飞快,怎么解决,加了 ORDER BY novel_chapter.UpTime Desc 执行速度是 1.2ms 去掉后 0.023ms
UpTime也做了索引,不过试了下,UpTime有索引跟没索引速度基本上一样,有何解决办法呢

converting HEAP to MyISAM 1.333%
Copying to tmp table 34.268%
Copying to tmp table on disk 60.152%

[ACMAIN_CHM]

创建 (BookName, UpTime)的复合索引

[ACMAIN_CHM]

参考一下这个贴子。

http://topic.csdn.net/u/20110827/17/739bd667-703b-4ca1-89ff-e625e724d00b.html

[sksgod]

创建了,没效果

[ACMAIN_CHM]

贴出你的EXPLAIN结果和所有相关表的show index from ..

[sksgod]

1 SIMPLE novel_chapter ALL BookName 68522 Using temporary; Using filesort
1 SIMPLE novel_booklist ref BookName BookName 130 novel.novel_chapter.BookName 1
1 SIMPLE novel_category ALL CategoryName 8 Using where; Using join buffer



novel_chapter 0 PRIMARY 1 ID A 68522 BTREE
novel_chapter 1 BookName 1 BookName A 243 YES BTREE
novel_chapter 1 BookName 2 UpTime A 68522 YES BTREE
novel_chapter 1 BookName 3 ChapterName A 68522 BTREE
novel_chapter 1 ID 1 ID A 68522 BTREE
novel_chapter 1 ChapterName 1 ID A 68522 BTREE
novel_chapter 1 ChapterName 2 ChapterName A 68522 BTREE
novel_chapter 1 ChapterName 3 Volume A 68522 YES BTREE
novel_chapter 1 ChapterName 4 ChapterLen A 68522 YES BTREE
novel_chapter 1 ChapterLen 1 ChapterLen A 68522 YES BTREE
novel_chapter 1 UpTime 1 UpTime A 22840 YES BTREE

[rucypli]

强制索引

select *
from A force index(1楼的索引)
where ...

[sksgod]

嘿嘿
这下快了,0.001ms

[sksgop]

顶0.001ms

[dingoolu]

学习，顶0.001ms