mysql - LEFT JOIN 性能问题 mysql

Question

这个查询工作正常并带来 598 行:

SELECT pdwspend.`Parent Supplier Name` , pdwspend.`Child Supplier ID` , pdwspend.`Cat     Tree - Cate` , sum( pdwspend.`Spend Value in LC` )
FROM pdwspend
WHERE pdwspend.Version = 'FINAL'
AND pdwspend.YearMonth >= '201310'
AND pdwspend.YearMonth <= '201409'
AND pdwspend.`Excludable` != 'Excluded'
AND pdwspend.`Year` = '2014'
AND pdwspend.`BU ID` = 'BU1'
GROUP BY pdwspend.`Parent Supplier Name` 

我想携带除 pdwspend.子供应商 ID = scraps.BW 父编号 之外的所有内容。应该有大约 70 个匹配项，因此，查询应该带来大约 528 行。这是我的查询:

SELECT pdwspend.`Parent Supplier Name` , pdwspend.`Child Supplier ID` , pdwspend.`Cat Tree - Cate` , sum( pdwspend.`Spend Value in LC` ) 
FROM pdwspend
LEFT JOIN scrubs ON  pdwspend.`Child Supplier ID`  = scrubs.`BW Parent Number`
WHERE pdwspend.Version = 'FINAL'
AND pdwspend.YearMonth >= '201310'
AND pdwspend.YearMonth <= '201409'
AND pdwspend.`Excludable` != 'Excluded'
AND pdwspend.`Year` = '2014'
AND pdwspend.`BU ID` = 'BU1'
AND scrubs.`BW Parent Number` IS NULL 
GROUP BY pdwspend.`Parent Supplier Name`

但是mySQL运行后就死机了。

请指教。

Answer 1

尝试使用不存在:

SELECT s.`Parent Supplier Name` , s.`Child Supplier ID` , s.`Cat     Tree - Cate` ,
       sum(s.`Spend Value in LC` )
FROM pdwspend s
WHERE s.Version = 'FINAL' AND s.YearMonth >= '201310' AND s.YearMonth <= '201409' AND
      s.`Excludable` <> 'Excluded' AND s.`Year` = '2014' AND s.`BU ID` = 'BU1' AND
      NOT EXISTS (SELECT 1
                  FROM scrubs sc
                  WHERE s.`Child Supplier ID` = sc.`BW Parent Number`
                 )
GROUP BY s.`Parent Supplier Name` ;

为了提高性能，您需要在 scrubs(BW Parent Number) 上建立索引。