MYSQL query, optimization

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,
                开发者_JAVA技巧      (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

---

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 

---

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.

---

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