mysql - 具有多个子选择连接的 MySQL 查询速度较慢

Question

这件事困扰了我一整天:

我有这个查询，它工作正常，但在某些情况下相当慢。解释结果告诉我 mysql 正在扫描 40000 行 3 次。查询连接近似相似的子选择，但都有一点不同。

我该如何改进？

SET @lastchecktime = (SELECT max(tresults.StartTime) FROM tresults);
SELECT tresults.shopID
 , tshops.OfficialName
 , tresults.Price, tresults.starttime
 , Sub2.minprice
 , Sub2.StartTime
 , Sub3.daystoolow
 , (sub2.minprice/tsupplierproducts.lowestprice) -1 as afwijking
 , If(maxstarttime = @lastchecktime,'yes' ,'no') as notavailable     
FROM
  tresults
INNER JOIN tshops
ON tshops.shopID = tresults.shopID
inner JOIN (SELECT tresults.shopID
                 , max(tresults.StartTime) AS MaxStartTime
            FROM
            tresults
            WHERE
              tresults.pID = 7
              AND tresults.websiteID = 1
              AND tresults.StartTime BETWEEN "2012-08-01" AND "2012-12-01"              
            GROUP BY
              tresults.shopID) Sub1
ON tresults.StartTime = Sub1.MaxStartTime AND Sub1.shopID = tshops.shopID
INNER JOIN (SELECT tresults.shopID
             , tresults.StartTime
             , min(tresults.Price) AS minprice
        FROM
          tresults
        WHERE
          tresults.pID = 7
          AND tresults.websiteID = 1
          AND tresults.StartTime BETWEEN "2012-08-01" AND "2012-12-01"
        GROUP BY
          tresults.shopID) Sub2
ON Sub2.shopID = tshops.shopID
INNER JOIN (SELECT tresults.shopID
             , round(count(tresults.StartTime)/3,0) AS daystoolow
        FROM
          tsupplierproducts
        INNER JOIN tresults
        ON tsupplierproducts.pID = tresults.pID AND tresults.Price < tsupplierproducts.LowestPrice
        WHERE
          tresults.pID = 7
          AND tresults.websiteID = 1
          AND tresults.StartTime BETWEEN "2012-08-01" AND "2012-12-01"
          AND tsupplierproducts.supplierID = 2
        GROUP BY
          tresults.shopID) Sub3
ON Sub3.shopID = tshops.shopID
INNER JOIN tsupplierproducts
ON tsupplierproducts.pID = tresults.pID AND tsupplierproducts.supplierID = 2
WHERE
  tresults.pID = 7
  AND tresults.websiteID = 1
ORDER BY
notavailable desc, tresults.Price DESC

解释结果:

1, PRIMARY, tsupplierproducts, const, PRIMARY,fk_SupplierID,fk_pID, PRIMARY, 8, const,const, 1, Using temporary; Using filesort
1, PRIMARY, <derived4>, ALL, , , , , 27, 
1, PRIMARY, <derived2>, ALL, , , , , 43, Using where; Using join buffer
1, PRIMARY, <derived3>, ALL, , , , , 43, Using where; Using join buffer
1, PRIMARY, tshops, eq_ref, PRIMARY, PRIMARY, 4, Sub1.shopID, 1, Using where
1, PRIMARY, tresults, eq_ref,     PRIMARY,idxPID,idxWebsite,idxStartTimeASC,idxStartTimeDESC,fk_shopID, PRIMARY, 20, Sub1.MaxStartTime,const,Sub3.shopID,const, 1, 
4, DERIVED, tsupplierproducts, const, PRIMARY,fk_SupplierID,fk_pID, PRIMARY, 8, , 1, Using temporary; Using filesort
4, DERIVED, tresults, ref, PRIMARY,idxPID,idxWebsite,idxStartTimeASC,idxStartTimeDESC,     idxPID, 4, , 42048, Using     where
3, DERIVED, tresults, ref, PRIMARY,idxPID,idxWebsite,idxStartTimeASC,idxStartTimeDESC,     idxPID, 4, , 42048,     Using     where; Using temporary; Using filesort
2, DERIVED, tresults, ref, PRIMARY,idxPID,idxWebsite,idxStartTimeASC,idxStartTimeDESC, idxPID, 4, , 42048, Using where; Using index; Using temporary; Using filesort

如有任何帮助，我们将不胜感激!

Answer 1

我的建议是首先删除子查询。