MySQL Order By减慢连接速度

Question

我对is查询有问题

SELECT *
FROM files f
LEFT JOIN xfiles x ON x.hash = f.bhash
LEFT JOIN cate c ON c.id = f.cate
LEFT JOIN users u ON u.id = f.user
LEFT JOIN userslev ul ON u.id_lev = ul.id
LEFT JOIN (
SELECT DISTINCT x.hash, s.iis
FROM anno x
JOIN seb s ON s.iis = x.icc
WHERE x.le =0
AND (
x.eve =0
OR x.eve =2
)
GROUP BY  `x`.`hash`
) AS tabel2 ON tabel2.hash = f.bhash
WHERE f.see + IFNULL( x.see, 0 ) + f.lee + IFNULL( x.lee, 0 ) >0
ORDER BY f.added DESC

显示第0-29行（共22083行，查询耗时9.6523秒）

正如你所看到的，它需要9秒！

但没有的"订单"

SELECT *
FROM files f
LEFT JOIN xfiles x ON x.hash = f.bhash
LEFT JOIN cate c ON c.id = f.cate
LEFT JOIN users u ON u.id = f.user
LEFT JOIN userslev ul ON u.id_lev = ul.id
LEFT JOIN (
SELECT DISTINCT x.hash, s.iis
FROM anno x
JOIN seb s ON s.iis = x.icc
WHERE x.le =0
AND (
x.eve =0
OR x.eve =2
)
GROUP BY  `x`.`hash`
) AS tabel2 ON tabel2.hash = f.bhash
WHERE f.see + IFNULL( x.see, 0 ) + f.lee + IFNULL( x.lee, 0 ) >0

显示第0-29行（共22083行，查询耗时0.0241秒）

仅0.0241秒：）

有没有一种方法可以在很好的时间内完成订单？

索引：

Indexes: (files)
Action  Keyname Type    Unique  Packed  Column  Cardinality Collation   Null    Comment
 Edit    Drop   PRIMARY BTREE   Yes No  ihash   27815   A       
 Edit    Drop   filename    BTREE   No  No  filename    27815   A       
 Edit    Drop   cate    BTREE   No  No  cate    45  A       
 Edit    Drop   user    BTREE   No  No  user    1264    A       
 Edit    Drop   bhash   BTREE   No  No  bhash (20)  27815   A       
 Edit    Drop   added   BTREE   No  No  added   27815   A       
 Edit    Drop   filename_2  FULLTEXT    No  No  filename    1           

Indexes: (xfiles)
Action  Keyname Type    Unique  Packed  Column  Cardinality Collation   Null    Comment
 Edit    Drop   PRIMARY BTREE   Yes No  id  8196    A       
 Edit    Drop   hash    BTREE   Yes No  hash    8196    A       
 Edit    Drop   see BTREE   No  No  see 37  A       
 Edit    Drop   leec    BTREE   No  No  lee25   A   

Indexes: (cate)
Action  Keyname Type    Unique  Packed  Column  Cardinality Collation   Null    Comment
 Edit    Drop   PRIMARY BTREE   Yes No  id  51  A   

Indexes: (users)
Action  Keyname Type    Unique  Packed  Column  Cardinality Collation   Null    Comment
 Edit    Drop   PRIMARY BTREE   Yes No  id  39776   A       
 Edit    Drop   username    BTREE   Yes No  username    39776   A       
 Edit    Drop   id_lev  BTREE   No  No  id_lev  19  A       

Indexes: (userslev)
Action  Keyname Type    Unique  Packed  Column  Cardinality Collation   Null    Comment
 Edit    Drop   base    BTREE   Yes No  id  22  A       

Indexes: (anno)
Action  Keyname Type    Unique  Packed  Column  Cardinality Collation   Null    Comment
 Edit    Drop   PRIMARY BTREE   Yes No  id  23636   A       
 Edit    Drop   icc BTREE   No  No  icc 2626    A       
 Edit    Drop   eve BTREE   No  No  eve 8   A       
 Edit    Drop   hash    BTREE   No  No  hash    5909    A       
 Edit    Drop   le  BTREE   No  No  le  875 A   

Indexes: (seb)
Action  Keyname Type    Unique  Packed  Column  Cardinality Collation   Null    Comment
 Edit    Drop   PRIMARY BTREE   Yes No  id  58  A       
 Edit    Drop   iis BTREE   Yes No  iis 58  A   YES 

解释：

id          select_type     table       type            possible_keys       key         key_len         ref         rows    Extra
1               PRIMARY     f           ALL             NULL                NULL        NULL            NULL        27819   Using temporary; Using filesort
1               PRIMARY     x           eq_ref          hash                hash        20              f.bhash     1       Using where
1               PRIMARY     c           eq_ref          PRIMARY             PRIMARY     4               f.cate      1   
1               PRIMARY     u           eq_ref          PRIMARY             PRIMARY     4               f.user      1   
1               PRIMARY     ul          eq_ref          base                base        4               u.id_lev    1   
1               PRIMARY     <derived2>  ALL             NULL                NULL        NULL            NULL        1176    
2               DERIVED     s           index           iis                 iis         5               NULL        58      Using index; Using temporary; Using filesort
2               DERIVED     x           ref             icc,eve,le          icc         4               s.iis       9       Using where

Answer 1

这里有很多关于优化订单的信息：http://dev.mysql.com/doc/refman/5.0/en/order-by-optimization.html

你能把这两个解释都发布出来吗？这样我们就可以看到查询计划中的区别了？