MySQL Join VS in+Subselect VS in+2 Abfragen

[Bububoomt]

Also ich hatte letztens eine Abfrage, bei der ich mich etwas gewundert habe, wie MSSQL (Leider igbts hier ja nur MYSQL als Prefix) diese abarbeitet.

Also die Abfrage ist ähnlich dieser:

Select x from y where id in (select d from z where a=2)

Nun diese Abfrage war extrem langsam. dann hatte ichs mit einem Join versucht, die ebenfalls sehr langsam war

(beides ca. 10-30 Sek.)

beim testen sind wir draufgekommen, wenn ich die Subselect manuell mache und dann einfüge also so:
Select x from y where id in (1,2,5,6,10,40,100...)
ist es sehr schnell (<1 Sek.).

 

[ice-breaker]

waren Subselect und Join zufälligerweise gleich schnell ?

 

[Bububoomt]

Gleichschnell glaube nicht, aber fast. Glaube Join war gering schneller.

 

[ice-breaker]

K, dann eine Vermutung (zumindest MySQL macht es, warum nicht auch MSSQL) Subselects werden oftmals in Joins umgewandelt, weil die Datenbank nicht korrekt testen kann, ob ein echter Subselect schneller oder langsamer als ein Join ist.

Würde dein Subselect 2 Millionen Datensätze auslesen, wäre die Where-Bedingung nicht mehr sinnvoll oder in irgendeiner Hinsicht performant lösbar (1), deswegen wäre an der Stelle ein Join sinnvoller.

Wenn die Datenbank also vorher nicht sinnvoll entscheiden kann, wird sie einfach den sicheren Weg gehen, als Programmierer weißt du natürlich besser als die Datenbank was da rauskommt und deswegen ist es teilweise wirklich sinnvoll, den Subselect zu emulieren.

(1) Hätte deine y-Tabelle 100k Einträge und dein Subselect-Ergebnis 2Mio Datensätze (m) würde es bedeuten, dass die Datenbank linear jeden Eintrag der y-Tabelle durchgehen müste und mit dem Ergebnis deines Subselects vergleichen müsste.
Wäre das Subselect-Ergebnis eine lineare Liste würde der mittlere Aufwand bei O(n * (m / 2)) liegen, also gigantisch.

 

[Bububoomt]

hmm, dachte da wären die Datenbanken doch etwas schlauer.

Nun, das Subselect hat momentan so an die 40 Ergebnisse, und die DB in der Gesucht wird, sind es dann ca 5 mio einträge.

 

[ice-breaker]

Die Datenbanken sind sehr schlau und haben sehr viele Optimierungen, die du ohne dich tief einzuarbeiten nicht kennst, das Problem ist nur wie so oft das Abwägen zwischen sinnvollen oft genutzten Optimierungen und Randoptimierungen.
Das Problem hier ist aber viel mehr, dass die Datenbank ausprobieren müsste, welches der bessere Weg ist, bei kleinen Datenbanken ist das kein Problem, bei großen jedoch tödlich

In Oracle wäre der Query kein Problem gewesen, die haben eine super Optimierung für Subquerys, MySQLs soll mit Version 6 verbessert werden, von MSSQL habe ich jedoch keine Ahnung