MYSQL查询、优化

Question

这是我在正在构建的页面中使用的 SQL 查询。它目前运行大约 8 秒并返回 12000 条记录，这是正确的，但我想知道您是否可以就如何使其更快提出可能的建议？

SELECT DISTINCT Advertiser.AdvertiserID, Business.Name, Business.Address1,    Business.Address2, Business.City, Business.State, Business.PostalCode, 
                  Business.Country, Business.Phone, Business.Fax, Business.Email, AdvertiserCategory.CategoryID, AdvertiserCategory.CategoryName AS Category,
                      (SELECT     MAX(PubDate) AS PubDate
                        FROM          NewsPaperAd
                        WHERE      (AdvertiserID = Advertiser.AdvertiserID)
                        GROUP BY AdvertiserID) AS PubDate
FROM Business INNER JOIN
                  Advertiser ON Business.BusinessID = Advertiser.AdvertiserID INNER JOIN
                  Tsheetrecipient ON Advertiser.AdvertiserID = Tsheetrecipient.AdvertiserID LEFT OUTER JOIN
                  AdvertiserCategory INNER JOIN
                  AdvertiserCategoryJoin ON AdvertiserCategory.CategoryID = AdvertiserCategoryJoin.CategoryID ON 
                  Advertiser.AdvertiserID = AdvertiserCategoryJoin.AdvertiserID
WHERE     ((SELECT     MAX(PubDate) AS PubDate
                     FROM         NewsPaperAd AS NewsPaperAd_1
                     WHERE     (AdvertiserID = Advertiser.AdvertiserID)
                     GROUP BY AdvertiserID) IS NOT NULL)
ORDER BY PubDate DESC

我真的很想知道 group by 子句有什么替代方案，因为这才是真正减慢速度的原因。

谢谢

Answer 1

此外，通过将 MAX( pubdate ) 的预查询作为第一个 FROM 表(别名)，并在 PUBDATE 的 WHERE NOT NULL 上进行过滤，您将仅获取具有发布日期的广告商。作为您的第一个查询，应该进行优化，获取较小的集合，然后加入广告商、业务、类别等。通过使用 STRAIGHT_JOIN，告诉优化器按照您提供的顺序执行此操作，因此它应该强制执行预查询-首先查询并使用 THAT 加入剩下的部分...

我将确保按 AdvertiserID 对 NewsPaperAd 建立索引，并对其余 JOIN 标准建立索引。如果这台机器上没有 MySQL，我唯一有问题的子句将是

其中 PubDate 不为空

SELECT STRAIGHT_JOIN DISTINCT 
      Advertiser.AdvertiserID,
      Business.Name,
      Business.Address1,
      Business.Address2,
      Business.City,
      Business.State,
      Business.PostalCode,
      Business.Country,
      Business.Phone,
      Business.Fax,
      Business.Email,
      AdvertiserCategory.CategoryID,
      AdvertiserCategory.CategoryName AS Category,
      QualifiedPubs.PubDate
   FROM 
      (SELECT AdvertiserID,
              MAX(PubDate) AS PubDate
          FROM 
              NewsPaperAd
          WHERE
              PubDate IS NOT NULL
          GROUP BY 
             AdvertiserID) AS QualifiedPubs
         INNER JOIN Advertiser 
            ON QualifiedPubs.AdvertiserID = Advertiser.AdvertiserID
            INNER JOIN Business 
               ON Advertiser.AdvertiserID = Business.BusinessID
            INNER JOIN Tsheetrecipient 
               ON Advertiser.AdvertiserID = Tsheetrecipient.AdvertiserID 
            INNER JOIN AdvertiserCategoryJoin 
               ON Advertiser.AdvertiserID = AdvertiserCategoryJoin.AdvertiserID 
               LEFT OUTER JOIN AdvertiserCategory 
                  ON AdvertiserCategoryJoin.CategoryID = AdvertiserCategory.CategoryID
    ORDER BY 
       PubDate DESC