Die Unterabfrage „SQL NOT IN“ führt zu Leistungseinbußen

Question

Ich möchte ein Formular verwenden, um Straßen zu überprüfen.

1. Abfrage ist nicht optimal
2. Die Abfragezeit ist zu lang

Kann mir jemand helfen

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;