Die Unterabfrage „SQL NOT IN' führt zu Leistungseinbußen
P粉6961462052023-09-12 12:21:35
0
2
601
Ich möchte ein Formular verwenden, um Straßen zu überprüfen.
Abfrage ist nicht optimal
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;
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
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;
没有使用ORDER BY时,查询结果是非确定性的(两次查询可能产生不同的结果)。因此强烈建议添加一些行排序。
索引
post_plz (adress, plz)
和adress (strasse, plz, id)
必须进行优化。You could try using aleft join between the table and check for not mactching values
你可以尝试在表之间使用左连接,并检查不匹配的值