SQL NOT IN子查詢導致效能下降的問題

Question

我想使用表格來檢查街道。

1. 查詢不是最優的
2. 查詢時間太長

有人可以幫我嗎

SELECT id, strasse, plz
FROM `adress`
WHERE strasse NOT IN (
    SELECT street
    FROM post_plz
    WHERE `street` like adress.strasse AND plz like adress.plz
)
LIMIT 5;

Answer 1

SELECT id, strasse, plz
FROM adress
WHERE NOT EXISTS ( SELECT NULL
                   FROM post_plz
                   WHERE post_plz.street = adress.strasse 
                     AND post_plz.plz = adress.plz )
-- ORDER BY {expression}  
LIMIT 5

沒有使用ORDER BY時，查詢結果是非確定性的（兩次查詢可能會產生不同的結果）。因此強烈建議添加一些行排序。

索引post_plz (adress, plz)和adress (strasse, plz, id)必須進行最佳化。

Answer 2

You could try using aleft join between the table and check for not mactching values

SELECT id, strasse, plz 

from `adress` 
left join  post_plz on strasse = street
  AND `street` like adress.strasse 
    AND plz like adress.plz
WHERE street is null
 LIMIT 5;

你可以嘗試在表之間使用左連接，並檢查不匹配的值

SELECT id, strasse, plz 

from `adress` 
left join  post_plz on strasse = street
  AND `street` like adress.strasse 
    AND plz like adress.plz
WHERE street is null
 LIMIT 5;