我在 PHP 網站上運行了這個 SQL 查詢。這是一個舊站點，查詢是幾年前由以前的開發人員構建的。但是現在隨著站點資料增加到 230mb 左右，這個查詢的執行速度變得很慢。大約需要 15-20 秒。有什么辦法可以讓這個運行得更快嗎？
SELECT DISTINCT
NULL AS bannerID,
C1.url AS url,
LOWER(C1.Organization) AS company_name,
CONCAT(
'https://mywebsite.co.uk/logos/',
C1.userlogo
) AS logo_url
FROM
Company AS C1
INNER JOIN Vacancy AS V1 ON LOWER(V1.company_name) = LOWER(C1.Organization)
WHERE
V1.LiveDate <= CURDATE()
AND url = ''
AND V1.ClosingDate >= CURDATE()
AND C1.flag_show_logo = 1
uj5u.com熱心網友回復：
正如評論的那樣，由于使用了lower函式，您的查詢無法進行查詢。
另外我懷疑你可以distinct通過使用exists而不是加入你的表來洗掉
select null as bannerID,
C1.url as url,
Lower(C1.Organization) as company_name,
Concat('https://mywebsite.co.uk/logos/', C1.userlogo) as logo_url
from Company c
where c.flag_show_logo = 1
and c.url = ''
and exists (
select * from Vacancy v
where v.LiveDate <= CURDATE()
and v.ClosingDate >= CURDATE()
and v.company_name = c.Organization
)
uj5u.com熱心網友回復：
通過更改為避免 sargable 問題
ON V1.company_name = C1.Organization
并將這兩列宣告為相同的排序規則，即以“_ci”結尾的排序規則。
并有這些復合索引：
C1: INDEX(flag_show_logo, url, Organization, userlogo)
V1: INDEX(company_name, LiveDate, ClosingDate)
（這些索引也應該有助于 Stu 的回答。）
轉載請註明出處，本文鏈接：https://www.uj5u.com/houduan/355531.html