CODEWITHSUNDEEP
mysqlquery-optimization
SirSmokealot
SirSmokealot

Reputation: 45

mySQL NOT IN SubQuery takes too long

I would like to check streets using a table.

  1. the query is not optimal
  2. 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;

Upvotes: 1

Views: 44

Answers (2)

Akina
Akina

Reputation: 42844

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.

Upvotes: 0

ScaisEdge
ScaisEdge

Reputation: 133400

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;

Upvotes: 1

Related Questions