MYSQL查询，优化

问题描述：

这是一个SQL查询，我正在使用一个页面，我正在构建。它目前运行约8秒钟，并返回12000条记录，这是正确的，但我想知道如果你可以提出可能的建议，我怎样才能使它更快？MYSQL查询，优化

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子句有哪些替代方案，因为这是真正放慢速度的原因。

谢谢

我不明白为什么这是标有视觉工作室2008年。 – 2011-03-02 19:30:53

真的不多 - 只是一个查询??如何提供你想要做什么的概述，关于表结构，引擎，索引，查询计划等的一些信息.... – 2011-03-02 21:17:38

答

您也可以将选择最大日期部分移动到from子句中，而不是将它放在select和where子句中。例如：

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, pd.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, 
        (SELECT AdvertiserID, MAX(PubDate) AS PubDate 
        FROM   NewsPaperAd 
        GROUP BY AdvertiserID) AS pd 
WHERE  
       pd.AdvertiserID = Advertiser.AdvertiserID AND pd.PubDate IS NOT NULL 
ORDER BY PubDate DESC

注意我没有测试这个查询，但它应该给你一个大概的想法。

答

在开始调整查询之前，您可能最好调整索引以提高查询性能。广告客户ID字段是否已编入索引？

答

此外，通过将MAX（pubdate）作为第一个FROM表（别名）的预查询，并且对PUBDATE的WHERE NOT NULL进行筛选，您将只获得具有发布日期的广告客户。随着你的第一个查询应该被优化，获得一个小集合，然后加入到广告商，业务，类别等。通过使用STRAIGHT_JOIN，告诉优化器按照你所呈现的顺序执行它，所以它应该强制pre - 查询第一，并使用THAT加入其余的路...

我会确保NewsPaperAd由AdvertiserID索引和其余JOIN标准的索引。如果没有MySQL的这台机器上，我唯一有问题的条款将是

WHERE pubdate的IS NOT NULL

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

MYSQL查询，优化

相关推荐