我有一个表和一个如下所示的查询.有关工作示例,请参阅此
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访问会导致比其他访问方法更高的成本,则不会创建任何索引,并且优化程序也不会丢失任何内容.
