mysql 子查询添加索引_mysql – 你能索引子查询吗？

我有一个表和一个如下所示的查询.有关工作示例,请参阅此

SQL Fiddle.

SELECT o.property_B, SUM(o.score1), w.score

FROM o

INNER JOIN

(

SELECT o.property_B, SUM(o.score2) AS score FROM o GROUP BY property_B

) w ON w.property_B = o.property_B

WHERE o.property_A = 'specific_A'

GROUP BY property_B;

使用我的真实数据,此查询需要27秒.但是,如果我首先创建w作为临时表和索引property_B,它们总共需要约1秒.

CREATE TEMPORARY TABLE w AS

SELECT o.property_B, SUM(o.score2) AS score FROM o GROUP BY property_B;

ALTER TABLE w ADD INDEX `property_B_idx` (property_B);

SELECT o.property_B, SUM(o.score1), w.score

FROM o

INNER JOIN w ON w.property_B = o.property_B

WHERE o.property_A = 'specific_A'

GROUP BY property_B;

DROP TABLE IF EXISTS w;

有没有办法结合这两个查询中最好的？即单个查询具有子查询中索引的速度优势？

编辑

在下面的Mehran回答之后,我在MySQL documentation中读到了这条解释：

从MySQL 5.6.3开始,优化器可以更有效地处理FROM子句中的子查询(即派生表)：

…

对于FROM子句中子查询需要实现的情况,优化器可以通过向实现表添加索引来加快对结果的访问.如果这样的索引允许对表进行ref访问,则可以大大减少在查询执行期间必须读取的数据量.请考虑以下查询：

SELECT * FROM t1

JOIN (SELECT * FROM t2) AS derived_t2 ON t1.f1=derived_t2.f1;

优化程序在derived_t2的列f1上构造索引,如果这样做,则允许对最低成本的执行计划使用ref访问.添加索引后,优化程序可以将具体化派生表视为与具有索引的常规表相同,并且它与生成的索引类似.与没有索引的查询执行成本相比,索引创建的开销可以忽略不计.如果ref访问会导致比其他访问方法更高的成本,则不会创建任何索引,并且优化程序也不会丢失任何内容.