mySQL NOT IN SubQuery takes too long

Question

I would like to check streets using a table.

the query is not optimal
the query takes much too long

Can someone 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;

ScaisEdge · Accepted Answer · 2022-03-08 20:47:09Z

1

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;

answered Mar 8, 2022 at 20:47

ScaisEdge

133k10 gold badges98 silver badges111 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

SirSmokealot Over a year ago

This query works just as well or just as poorly. Apparently the zip codes are not checked properly!? However, the runtime had not improved (about 80 seconds) Only when I declared the column street as index, the script ran within 0.4 seconds. It does not run perfectly, but for my purposes it is enough. Thanxx

Akina · Accepted Answer · 2022-03-08 20:55:52Z

0

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

Without ORDER BY the query returns non-deterministic output (two query executions may produce different outputs). So it is strongly recommended to add some rows ordering.

The indices post_plz (adress, plz) and adress (strasse, plz, id) must improve.

answered Mar 8, 2022 at 20:55

Akina

43.2k6 gold badges17 silver badges30 bronze badges

Collectives™ on Stack Overflow

mySQL NOT IN SubQuery takes too long

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related