MYSQL query, optimization

Question

This is a sql query that I am using in a page that I am building. It currently runs about 8 seconds and returns 12000 records, which is correct but I am wondering if you could make possible suggestion as to how I could make it faster?

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

I'm really wondering what alternatives there are to the group by clause as this is what is really slowing it down.

Thanks

not much to go on really - just a query ?? how about providing an overview of what you're trying to do, some info about table structures, engines, indexes, query plans etc.... — Jon Black
– Jon Black, Commented Mar 2, 2011 at 21:17

DRapp · Accepted Answer · 2011-03-02 20:07:26Z

Additionally, by having the prequery of MAX( pubdate ) as the first FROM table (alias), and that filtering on WHERE NOT NULL of the PUBDATE, you will only GET advertisers that HAVE a publication date. With THAT being your first query should be optimized, get small set, THEN join to the advertisers, business, categories, etc. By using STRAIGHT_JOIN, tells the optimizer to do it in the order you've presented, so it should FORCE the pre-query first and use THAT to join the rest of the way down...

I would ensure index on NewsPaperAd by AdvertiserID and index on the rest of the JOIN criteria. Without MySQL on this machine, my only questionable clause would be the

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

Braden Becker · Accepted Answer · 2011-03-02 19:33:20Z

You could also move the select max date part into your from clause instead of having it in your select and where clauses. Something like:

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

Note I didn't test this query but it should give you a general idea.

JohnFx · Accepted Answer · 2011-03-02 19:27:39Z

0

Before you start tuning the query, you are probably better off tuning the indexes to improve the query performance. Is the field AdvertiserID indexed?

answered Mar 2, 2011 at 19:27

JohnFx

35k19 gold badges108 silver badges169 bronze badges

Collectives™ on Stack Overflow

MYSQL query, optimization

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related