SQL NOT IN subquery causes performance degradation

Question

I want to use a table to check the streets.

1. Query is not optimal
2. Query time is too long

Can anyone help me

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

When ORDER BY is not used, the query results are non-deterministic (two queries may produce different results). So adding some row sorting is highly recommended.

Index post_plz (adress, plz) and adress (strasse, plz, id) must be optimized.

Answer 2

You could try using aleft join between the table and check for not matching 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;

You can try using a left join between the tables and check for mismatched 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;