不知道哪来的filesort！ ACMAIN_CHM等请进！

[netxuning]

mysql> EXPLAIN SELECT SQL_NO_CACHE STRAIGHT_JOIN E_info.infoID, E_info.info_time, E_role_info.roleID, E_role_info.related FROM E_role_info INNER JOIN E_info ON E_info.infoID = E_role_info.infoID WHERE E_role_info.roleID = 413382 ORDER BY E_info.info_time DESC LIMIT 10;                                    
+----+-------------+-------------+------+-------------------------------------------+-------------+---------+-------------------------------+---------+---------------------------------+
| id | select_type | table       | type | possible_keys                             | key         | key_len | ref                           | rows    | Extra                           |
+----+-------------+-------------+------+-------------------------------------------+-------------+---------+-------------------------------+---------+---------------------------------+
|  1 | SIMPLE      | E_role_info | ref  | PRIMARY,idx_E_role_info_roleID,idx_roleID | idx_roleID  | 3       | const                         | 2508851 | Using temporary; Using filesort | 
|  1 | SIMPLE      | E_info      | ref  | PRIMARY,idx_id_time                       | idx_id_time | 4       | news_data2.E_role_info.infoID |       1 | Using index                     | 
+----+-------------+-------------+------+-------------------------------------------+-------------+---------+-------------------------------+---------+---------------------------------+

我所不清楚的是，该语句ORDER BY E_info.info_time 是按照E_info表中的info_time来排序，并没有要求按照E_role_info表中的任何字段排序，但为什么会在该表中的extra字段出现Using temporary; Using filesort ?
能否通过优化，消除这个filesort呢？

[ACMAIN_CHM]

WHERE E_role_info.roleID = 413382
ORDER BY E_info.info_time

这种情况下，在 where 之后，做 join ，JOIN完后的数据是没有索引可利用。这时MySQL只能进行全部记录集中的排序。这么多记录(2508851), 不可以在内存中完成，因此会用到临时表进行排序。

[vinsonshen]

因为你在联接的基础上又有了条件“WHERE E_role_info.roleID = 413382 ”，所以，“Using temporary; Using filesort”会在表E_role_info的临时结果集上进行进行；若没这个“WHERE E_role_info.roleID = 413382 ”条件，则“Using temporary; Using filesort”会在进行order by的字段的对应表上进行（以你这里的查询语句为例的话，则是进行“ORDER BY E_info.info_time ”的所属表E_info）。

[vinsonshen]

建议你参考下oracle里面的前缀表的相关信息看看

[netxuning]

谢谢二位，如果仅仅从建立索引，优化sql语句的层面上讲，有没有优化的余地呢？

[ACMAIN_CHM]

没有什么好办法，和你自己的想法一样，把 info_time 加入到表E_role_info中，然后创建索引

[netxuning]

查看了一下profile,大部分时间都花在了拷贝临时表上,排序也花了1秒7多，不清楚这个排序是哪里的排序！

mysql> SET PROFILING = 1;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT SQL_NO_CACHE STRAIGHT_JOIN E_info.infoID, E_info.info_time, E_role_info.roleID, E_role_info.related FROM E_role_info INNER JOIN E_info ON E_info.infoID = E_role_info.infoID WHERE E_role_info.roleID = 413382 ORDER BY E_info.info_time DESC LIMIT 10;
+----------+---------------------+--------+---------+
| infoID   | info_time           | roleID | related |
+----------+---------------------+--------+---------+
| 42880323 | 2009-04-18 08:58:56 | 413382 |       0 | 
| 42880311 | 2009-04-18 08:58:24 | 413382 |       0 | 
| 42880353 | 2009-04-18 08:58:21 | 413382 |       0 | 
| 42880347 | 2009-04-18 08:58:21 | 413382 |       0 | 
| 42880348 | 2009-04-18 08:58:21 | 413382 |       0 | 
| 42880351 | 2009-04-18 08:58:20 | 413382 |       0 | 
| 42880340 | 2009-04-18 08:57:59 | 413382 |       0 | 
| 42880288 | 2009-04-18 08:57:45 | 413382 |       0 | 
| 42880314 | 2009-04-18 08:57:31 | 413382 |       0 | 
| 42880306 | 2009-04-18 08:57:31 | 413382 |       0 | 
+----------+---------------------+--------+---------+
10 rows in set (24.82 sec)

mysql> SHOW PROFILE;
+--------------------------------+-----------+
| Status                         | Duration  |
+--------------------------------+-----------+
| (initialization)               | 0.000002  | 
| checking query cache for query | 0.000079  | 
| Opening tables                 | 0.000012  | 
| System lock                    | 0.000006  | 
| Table lock                     | 0.000007  | 
| init                           | 0.000023  | 
| optimizing                     | 0.000014  | 
| statistics                     | 0.0000900 | 
| preparing                      | 0.000018  | 
| Creating tmp table             | 0.000026  | 
| executing                      | 0.000003  | 
| Copying to tmp table           | 6.206605  | 
| converting HEAP to MyISAM      | 0.101729  | 
| Copying to tmp table on disk   | 16.788693 | 
| Sorting result                 | 1.722166  | 
| Sending data                   | 0.000234  | 
| end                            | 0.000003  | 
| removing tmp table             | 0.012298  | 
| end                            | 0.000007  | 
| query end                      | 0.000002  | 
| freeing items                  | 0.00001   | 
| closing tables                 | 0.000006  | 
| logging slow query             | 0.000002  | 
+--------------------------------+-----------+
23 rows in set (0.00 sec)

[netxuning]

查看了一下profile,大部分时间都花在了拷贝临时表上,排序也花了1秒7多，不清楚这个排序排的是谁的序！

mysql> SET PROFILING = 1;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT SQL_NO_CACHE STRAIGHT_JOIN E_info.infoID, E_info.info_time, E_role_info.roleID, E_role_info.related FROM E_role_info INNER JOIN E_info ON E_info.infoID = E_role_info.infoID WHERE E_role_info.roleID = 413382 ORDER BY E_info.info_time DESC LIMIT 10;
+----------+---------------------+--------+---------+
| infoID   | info_time           | roleID | related |
+----------+---------------------+--------+---------+
| 42880323 | 2009-04-18 08:58:56 | 413382 |       0 | 
| 42880311 | 2009-04-18 08:58:24 | 413382 |       0 | 
| 42880353 | 2009-04-18 08:58:21 | 413382 |       0 | 
| 42880347 | 2009-04-18 08:58:21 | 413382 |       0 | 
| 42880348 | 2009-04-18 08:58:21 | 413382 |       0 | 
| 42880351 | 2009-04-18 08:58:20 | 413382 |       0 | 
| 42880340 | 2009-04-18 08:57:59 | 413382 |       0 | 
| 42880288 | 2009-04-18 08:57:45 | 413382 |       0 | 
| 42880314 | 2009-04-18 08:57:31 | 413382 |       0 | 
| 42880306 | 2009-04-18 08:57:31 | 413382 |       0 | 
+----------+---------------------+--------+---------+
10 rows in set (24.82 sec)

mysql> SHOW PROFILE;
+--------------------------------+-----------+
| Status                         | Duration  |
+--------------------------------+-----------+
| (initialization)               | 0.000002  | 
| checking query cache for query | 0.000079  | 
| Opening tables                 | 0.000012  | 
| System lock                    | 0.000006  | 
| Table lock                     | 0.000007  | 
| init                           | 0.000023  | 
| optimizing                     | 0.000014  | 
| statistics                     | 0.0000900 | 
| preparing                      | 0.000018  | 
| Creating tmp table             | 0.000026  | 
| executing                      | 0.000003  | 
| Copying to tmp table           | 6.206605  | 
| converting HEAP to MyISAM      | 0.101729  | 
| Copying to tmp table on disk   | 16.788693 | 
| Sorting result                 | 1.722166  | 
| Sending data                   | 0.000234  | 
| end                            | 0.000003  | 
| removing tmp table             | 0.012298  | 
| end                            | 0.000007  | 
| query end                      | 0.000002  | 
| freeing items                  | 0.00001   | 
| closing tables                 | 0.000006  | 
| logging slow query             | 0.000002  | 
+--------------------------------+-----------+
23 rows in set (0.00 sec)