Vereinfachung von Mysql-querys

[Programmierer]

Ich würde gerne folgenden Ausdruck mit JOINs vereinfachen wenn möglich:

  
SELECT id  
FROM lektionen  
WHERE name LIKE '%test%'  
OR id  
IN (  
  
SELECT lektionenid  
FROM woerter  
WHERE sprache1 LIKE '%test%'  
OR sprache2 LIKE '%test%'  
)  

Kann man diesen Subquery aus dem While-Block in ein Join verwandeln?

[Hopsel]

Hi Programmierer!

Kann man diesen Subquery aus dem While-Block in ein Join verwandeln?

Wozu? Ein Join ist normalerweise dazu da, die Projektionsliste zu erweitern, also Attribute (Spalten) weiterer Relationen zur Ergebnismenge hinzuzufügen.

Die WHERE-Klausel dagegen selektiert die Ergebnismenge, gibt also Bedingungen an, mit denen du nur gewisse Zeilen als Resultat bekommst.

Die Unterabfrage ist hier kein Problem und mMn die performanteste Lösung.

MfG H☼psel

[Programmierer]

Vielen Dank für deine Nachricht...

Das Problem ist jedoch etwas verzwickter.

Beschreibung:

Es handelt sich um einen Vokabeltrainer. Die Wörter (Fremdwörter) sind in Lektionen und Bücher geordnet. Also wie eine Hierarchie: Bücher - Lektionen - Wörter

Ich habe drei Tabellen:

buecher (ca. 1500 Datensätze):
+----+-----------+-----------+
|id  |name       |userid     |
+----+-----------+-----------+

lektionen (ca. 24'000 Datensätze):
+----+-----------+-----------+
|id  |name       |buchid     |
+----+-----------+-----------+

woerter (ca. 900'000 Datensätze):
+----+-----------+-----------+-----------+
|id  |sprache1   |sprache2   |lektionenid|
+----+-----------+-----------+-----------+

"name" ist immer der Name des Buches / der Lektion
"userid" ist eine Session-Variable, damit nur die "eigenen" Bücher angezeigt werden können (auf dieser Spalte ist ein Index)
"buchid" weist die Lektion einem Buch zu (auf dieser Spalte ist ein Index)
"lektionenid" weist das Wort einer Lektion zu (auf dieser Spalte ist ein Index)
"sprache1" ist das Wort in der Muttersprache
"sprache2" ist das Wort in der Fremdsprache

Da ich (wie oben geschrieben) mit sehr vielen Datensätzen hantiere, muss ich die Abfragen sehr effizient gestalten.

Wenn nun alle eigenen Bücher (userid = $_SESSION["userid"]) angezeigt werden, habe ich eine Suche gemacht, die sofort nach jeder Eingabe eines Buchstabens die ganzen "eigenen" Bücher, Lektionen und Wörter durchgeht und prüft, ob die aktuelle Eingabe im Buchnamen, Lektionennamen, sprache1 oder sprache2 vorhanden ist und die dazugehörigen Buch-ID ausgibt. Dazu habe ich folgende Abfrage geschrieben:

  
SELECT id  
FROM buecher  
WHERE userid = '<?= $_SESSION["userid"] ?>'  
AND (  
name LIKE '%eingabe%'  
OR id  
IN (  
  
SELECT buchid  
FROM lektionen  
WHERE name LIKE '%eingabe%'  
)  
OR id  
IN (  
  
SELECT (  
  
SELECT buchid  
FROM lektionen  
WHERE id = woerter.lektionenid  
)  
FROM woerter  
WHERE (  
sprache1 LIKE '%eingabe%'  
OR sprache2 LIKE '%eingabe%'  
)  
)  
)  

Diese Abfrage dauert bei mir jedoch ca. 22 Sekunden, was viel zu lang ist!
Kann ich diese Subquerys nicht verbessern, damit z.B. nicht mehr die ganze Wörter-Tabelle durchgetestet werden muss (habe das mit EXPLAIN SELECT gesehen, dass alle wörter durchgetestet werden müssen).

Ich hoffe, ihr versteht mein Problem...

[Vinzenz Mai]

Hallo,

Wenn nun alle eigenen Bücher (userid = $_SESSION["userid"]) angezeigt werden, habe ich eine Suche gemacht, die sofort nach jeder Eingabe eines Buchstabens die ganzen "eigenen" Bücher, Lektionen und Wörter durchgeht und prüft, ob die aktuelle Eingabe im Buchnamen, Lektionennamen, sprache1 oder sprache2 vorhanden ist und die dazugehörigen Buch-ID ausgibt. Dazu habe ich folgende Abfrage geschrieben:

Diese Abfrage dauert bei mir jedoch ca. 22 Sekunden, was viel zu lang ist!
Kann ich diese Subquerys nicht verbessern, damit z.B. nicht mehr die ganze Wörter-Tabelle durchgetestet werden muss (habe das mit EXPLAIN SELECT gesehen, dass alle wörter durchgetestet werden müssen).

mit einer Volltextsuche, sonst nicht.

Abfragen vom Typ LIKE '%irgendwas%' können grundsätzlich *nicht* von einem Index profitieren und sind *immer* langsam. In Deinem Fall sehr spürbar langsam. Solche Abfragen nach jedem Buchstaben abzufeuern ist erst recht keine gute Idee.

Ach ja, Deine beiden bisherigen Fragen zum gleichen Thema waren unzulässige Vereinfachungen:

</archiv/2010/6/t198397/#m1332267>
</archiv/2010/3/t196307/#m1315065>

Das Jokerzeichen % zu Beginn verhindert effektiv, dass Indexe genutzt werden können. Ohne Index *muss* jeder Datensatz einzeln geprüft werden. Wegen OR je Datensatz zwei Spalten.

Freundliche Grüße

Vinzenz

[Programmierer]

D.h. ich kann diese Volltextsuche ist nicht performancegerecht umzusetzen? Gibt es keine anderen Wege?

Abfragen vom Typ LIKE '%irgendwas%' können grundsätzlich *nicht* von einem Index profitieren und sind *immer* langsam. In Deinem Fall sehr spürbar langsam. Solche Abfragen nach jedem Buchstaben abzufeuern ist erst recht keine gute Idee.

Das möchte ich eben, da ich eine Tabelle mit allen Büchern habe und bei jeder Tastatureingabe die Tabellenzeilen ausblenden möchte, die nicht mit den Suchkriterien übereinstimmen.

Ach ja, Deine beiden bisherigen Fragen zum gleichen Thema waren unzulässige Vereinfachungen:

Was bedeutet "unzulässige Vereinfachungen"?

[Vinzenz Mai]

Hallo,

D.h. ich kann diese Volltextsuche ist nicht performancegerecht umzusetzen? Gibt es keine anderen Wege?

doch: die Volltextsuche von MySQL verwenden.

Ach ja, Deine beiden bisherigen Fragen zum gleichen Thema waren unzulässige Vereinfachungen:

Was bedeutet "unzulässige Vereinfachungen"?

nun: was ich unter den Links schrieb:

Das Jokerzeichen % zu Beginn verhindert effektiv, dass Indexe genutzt werden können. Ohne Index *muss* jeder Datensatz einzeln geprüft werden. Wegen OR je Datensatz zwei Spalten.

Hättest Du damals schon diese Art von Suche beschrieben, hätte man Dir dies gleich sagen können - und Dir gleich die Volltextsuche nahelegen können.

Freundliche Grüße

Vinzenz

[Ilja]

moin,

Ein Join ist normalerweise dazu da, die Projektionsliste zu erweitern, also Attribute (Spalten) weiterer Relationen zur Ergebnismenge hinzuzufügen.

wenn man das macht, dann läuft man sehr schnell gefahr, eine falsche ergebnismenge zu bekommen. man muss schon sehr genau hinschauen, was man da alles joint. man sollte nicht immer joinen, nur um andere attribute in der abfrage anzuzeigen.

Ilja

[Hopsel]

Hi Ilja!

wenn man das macht, dann läuft man sehr schnell gefahr, eine falsche ergebnismenge zu bekommen. man muss schon sehr genau hinschauen, was man da alles joint. man sollte nicht immer joinen, nur um andere attribute in der abfrage anzuzeigen.

Ich habe ja nicht behauptet, dass Joins einfach zu verstehen sind. =)
Dennoch ist ein Join nicht dazu da, die Ergebnismenge zu selektieren. Auch wenn er schnell zu einer Verfielfachung der Ergebnismenge führen kann.

MfG H☼psel

[Ilja]

moin,

Dennoch ist ein Join nicht dazu da, die Ergebnismenge zu selektieren. Auch wenn er schnell zu einer Verfielfachung der Ergebnismenge führen kann.

doch, ganz genau dafür ist sie primär da.

Ilja

[Hopsel]

Hi Ilja!

Dennoch ist ein Join nicht dazu da, die Ergebnismenge zu selektieren. Auch wenn er schnell zu einer Verfielfachung der Ergebnismenge führen kann.
doch, ganz genau dafür ist sie primär da.

Wenn ich zu einem Fahrzeug zusätzlich alle Werkstattbesuche auslesen möchte, dann erweitere ich mittels eines Joins die Projektionsliste, nicht die Ergebnismenge (Ergebnismenge ist hier die Anzahl der selektierten Datensätze, vielleicht haben wir uns da falsch verstanden). Dass dadurch Anzahl der Ergebniszeilen vergrößert werden kann, ist doch nebensächlich. Die Anzahl der Fahrzeuge bleibt doch gleich, oder?

MfG H☼psel

[Ilja]

moin,

das hier....

dann erweitere ich mittels eines Joins die Projektionsliste, nicht die Ergebnismenge (Ergebnismenge ist hier die Anzahl der selektierten Datensätze, vielleicht haben wir uns da falsch verstanden).

steht im widerspruch zu dem hier....

Dass dadurch Anzahl der Ergebniszeilen vergrößert werden kann, ist doch nebensächlich. Die Anzahl der Fahrzeuge bleibt doch gleich, oder?

wenn du joints, zeigst du eben nicht mehr nur die fahrzeuge an. und wenn du nicht aufpasst, verändert sich durch einen INNER JOIN sogar die anzahl der distincten fahrzeuge. dann sind nicht mehr die dabei, die noch keinen einzigen fahrzeugbesuch hatten. joins in der hautpsache für die projektion zu benutzen ist gefährlich, sehr sogar. in der hauptsache dienen sie für mich dazu, die richtige anzahl der ergebnismenge zu bekommen, sprich zu selektieren. wenn ich dann schon die attribute dabei habe, die ich brauche, um so besser. wenn mir aber attribute fehlen, dann verwende ich entweder korrelierte unterabfragen oder aber ich joine mit bedacht und ganz bewußt unter der berücksichtigung von beziehungsarten und das auch nur sehr ungerne.

Ilja

[Hopsel]

Hi Ilja!

Okay, ich habe gerade nochmal drüber nachgedacht und natürlich hast du recht. Natürlich dienen Joins auch dazu, um gewisse Datensätze zu selektieren und somit die Ergebnismenge einzuschränken. Dabei kann man dann auch die Projektionsliste auf die Spalten der verknüpften Datenbank ausweiten.

Ich dachte halt, ich kann mich irgendwie rauswinden, aber sowas scheint bei Selfhtml nicht zu funzen(TM). ;-)

MfG H☼psel

[Ilja]

moin,

Okay, ich habe gerade nochmal drüber nachgedacht und natürlich hast du recht. Natürlich dienen Joins auch dazu, um gewisse Datensätze zu selektieren und somit die Ergebnismenge einzuschränken.

oder eben die ergebnismenge zu vergrößern, auf jeden fall haben joins in der hauptsache einen einfluss auf die ergebnismenge. nicht immer, zum beispiel, wenn es sich um eine 1:1 muss beziehung handelt. dann sollte die ergebnismenge in der tat gleich bleiben. aber das ist dann wie gesagt ein sonderfall und nicht die regel.

Ich dachte halt, ich kann mich irgendwie rauswinden, aber sowas scheint bei Selfhtml nicht zu funzen(TM). ;-)

du hast letzlich die gleiche meinung über joins gehabt, wie 99% aller anderen, insofern warst du schon mal auf der seite der großen mehrheit. trotzdem mein geliebter leitspruch zu dem thema: "Joins sind böse"....

Ilja