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

Question

一条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_binCREATE 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_binCREATE 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先谢过各位。

Answer 1

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

Answer 2

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

Answer 3

试了上面朋友的方法，已然如故。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)

Answer 4

没有办法，你的这个是多表查询后排序。参考下贴中的讨论。http://topic.csdn.net/u/20090520/16/a96a2e90-a935-4460-837e-e52b4557c519.html?79650