Trung tâm cá nhân
sách gpt4 ai đã đi

Truy vấn và tối ưu hóa MYSQL

In lại 作者：行者123 更新时间：2023-11-29 14:53:40 31 4
mua khóa gpt4 Nike

这是我在正在构建的页面中使用的 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 子句有什么替代方案，因为这才是真正减慢速度的原因。

Cảm ơn

1 Câu trả lời

此外，通过将 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

关于MYSQL查询、优化，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/5172311/

31 4 0
Bài viết được đề xuất: java - 优化 MySQL 中的日期查询
Bài viết được đề xuất: mysql innodb 和 myisam 之间的更新时间
Bài viết được đề xuất: mysql - 要更新的 odbc 语法
Bài viết được đề xuất: android - STREAM_VOICE_CALL 进行时，为什么其他应用程序能听到声音？
行者123
Hồ sơ cá nhân

Tôi là một lập trình viên xuất sắc, rất giỏi!

Nhận phiếu giảm giá Didi Taxi miễn phí
Mã giảm giá Didi Taxi
Các bài viết nóng hổi trên toàn bộ trang web
Giấy chứng nhận ICP Bắc Kinh số 000000
Hợp tác quảng cáo: 1813099741@qq.com 6ren.com