请教ORDER BY的查询优化：如何解决"Using temporary; Using filesort"

[石榴刺猬]

一条SQL语句MySQL解释器给了一个"Using temporary; Using filesort"。
每个表的相关字段都有索引，包括ORDER BY中的commits.date。去掉ORDER BY就没有"Using temporary; Using filesort"了。

查了很多资料、做了各种尝试（在各个相关字段上建索引）都无法解决。请教这是何故？如何优化才能去掉"Using temporary; Using filesort"？

SQL语句如下：
SELECT commits.sha, commit_component.component
FROM commits
JOIN commit_from ON commits.sha=commit_from.sha
JOIN commit_component ON commits.sha=commit_component.sha
WHERE commit_from.propagation=0
ORDER BY commits.date


mysql> explain SELECT commits.sha, commit_component.component
-> FROM commits
-> JOIN commit_from ON commits.sha=commit_from.sha
-> JOIN commit_component ON commits.sha=commit_component.sha
-> WHERE commit_from.propagation=0
-> ORDER BY commits.date \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: commit_component
type: ALL
possible_keys: PRIMARY,commit_component_sha
key: NULL
key_len: NULL
ref: NULL
rows: 81444
Extra: Using temporary; Using filesort
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: commits
type: eq_ref
possible_keys: PRIMARY,commits_full
key: PRIMARY
key_len: 122
ref: propagation.commit_component.sha
rows: 1
Extra:
*************************** 3. row ***************************
id: 1
select_type: SIMPLE
table: commit_from
type: eq_ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 126
ref: propagation.commit_component.sha,const
rows: 1
Extra: Using index
3 rows in set (0.00 sec)



数据表相关结构信息如下：

CREATE TABLE `commits` (
`repo` int(11) NOT NULL,
`branch` int(11) NOT NULL,
`sha` varchar(40) collate utf8_bin NOT NULL,
`user_id` int(11) NOT NULL,
`date` timestamp NOT NULL default '0000-00-00 00:00:00',
`noprop` tinyint(1) NOT NULL default '0',
`jira` tinytext collate utf8_bin,
`gerrit` tinytext collate utf8_bin,
`body` text collate utf8_bin NOT NULL,
PRIMARY KEY (`sha`),
KEY `user_id` (`user_id`),
KEY `branch` (`branch`,`user_id`),
KEY `repo` (`repo`,`branch`),
KEY `branch_2` (`branch`,`date`),
KEY `commits_full` (`sha`,`date`,`jira`(20),`gerrit`(20),`body`(20)),
KEY `commit_date` (`date`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin

CREATE TABLE `commit_component` (
`sha` varchar(40) collate utf8_bin NOT NULL,
`component` tinytext collate utf8_bin NOT NULL,
PRIMARY KEY (`sha`),
KEY `commit_component_sha` (`sha`,`component`(20))
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin

CREATE TABLE `commit_from` (
`sha` varchar(40) collate utf8_bin NOT NULL,
`propagation` int(11) NOT NULL,
PRIMARY KEY (`sha`,`propagation`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin

先谢过各位。

[石榴刺猬]

试了上面朋友的方法，已然如故。

mysql> explain SELECT commits.sha, commit_component.component
-> FROM commits force index(commits_full)
-> JOIN commit_from ON commits.sha=commit_from.sha
-> JOIN commit_component force index (commit_component_sha) ON commits.sha=commit_component.sha
-> WHERE commit_from.propagation=0
-> ORDER BY commits.date
-> \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: commit_component
type: ALL
possible_keys: commit_component_sha
key: NULL
key_len: NULL
ref: NULL
rows: 81596
Extra: Using temporary; Using filesort
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: commits
type: ref
possible_keys: commits_full
key: commits_full
key_len: 122
ref: propagation.commit_component.sha
rows: 1
Extra: Using index
*************************** 3. row ***************************
id: 1
select_type: SIMPLE
table: commit_from
type: eq_ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 126
ref: propagation.commits.sha,const
rows: 1
Extra: Using where; Using index
3 rows in set (0.00 sec)

[iihero]

无论是否建了索引，排序都要用到临时空间的。除非你把缓冲设得足够大。

[ACMAIN_CHM]

没有办法，你的这个是多表查询后排序。参考下贴中的讨论。

http://topic.csdn.net/u/20090520/16/a96a2e90-a935-4460-837e-e52b4557c519.html?79650

[rucypli]

SELECT commits.sha, commit_component.component
FROM commits force index(commits_full)
JOIN commit_from ON commits.sha=commit_from.sha
JOIN commit_component force index (commit_component_sha) ON commits.sha=commit_component.sha
WHERE commit_from.propagation=0
ORDER BY commits.date

[WWWWA]

强制使用`commits_full`这个索引试试