EXPLAIN 在此查询上

select v.type,sum(c.rank)
from
  (select distinct power,color,type from vehicle) v
  join configuration c using (power,color)
group by v.type

给予

+----+-------------+---------------+------------+-------+---------------+-------------+---------+-----------------------------------------+---------+----------+---------------------------------+
| id | select_type |     table     | partitions | type  | possible_keys |     key     | key_len |                   ref                   |  rows   | filtered |              Extra              |
+----+-------------+---------------+------------+-------+---------------+-------------+---------+-----------------------------------------+---------+----------+---------------------------------+
|  1 | PRIMARY     | configuration | NULL       | ALL   | veh           | NULL        | NULL    | NULL                                    |   76658 |   100.00 | Using temporary; Using filesort |
|  1 | PRIMARY     | <derived2>    | NULL       | ref   | <auto_key0>   | <auto_key0> | 6       | configuration.power,configuration.color |      65 |   100.00 | NULL                            |
|  2 | DERIVED     | vehicle       | NULL       | index | cov           | cov         | 20      | NULL                                    | 5058658 |   100.00 | Using index                     |
+----+-------------+---------------+------------+-------+---------------+-------------+---------+-----------------------------------------+---------+----------+---------------------------------+

即使我设置了 force index 如果我使用表而不是子查询

create table tmp select distinct power,color,type from vehicle

那么 Explain 在“相同”查询上

select v.type,sum(c.rank)
from
  tmp v 
  join configuration c using (power,color)
group by type

变成

+----+-------------+---------------+------------+------+---------------+------+---------+---------------------+---------+----------+---------------------------------+
| id | select_type |     table     | partitions | type | possible_keys | key  | key_len |         ref         |  rows   | filtered |              Extra              |
+----+-------------+---------------+------------+------+---------------+------+---------+---------------------+---------+----------+---------------------------------+
|  1 | SIMPLE      | tmp           | NULL       | ALL  | NULL          | NULL | NULL    | NULL                | 1016144 |   100.00 | Using temporary; Using filesort |
|  1 | SIMPLE      | configuration | NULL       | ref  | veh           | veh  | 6       | tmp.power,tmp.color |       2 |   100.00 | NULL                            |
+----+-------------+---------------+------------+------+---------------+------+---------+---------------------+---------+----------+---------------------------------+

这个速度快了4倍
我怎样才能避免使用硬table？