SQL Query optimieren

[Klaus]

Hallo Forum,

folgende Query läuft (fast) ewig und ich frage mal in die Runde, ob das besser geht als ich es gemacht habe?

  
SELECT m.1,m.2,m.3  
FROM table1 m  
LEFT JOIN table2 rc ON rc.mID = m.mID  
LEFT JOIN table3 r ON rc.rID = r.rID  
WHERE m.5 != '' AND  
m.mID NOT  
IN (  
SELECT mID  
FROM table2 WHERE mID IS NOT NULL  
)  

table 1 hat ca. 12000 Einträge,
table 2 hat ca. 6500 und
table 3 ca. 5500 Einträge.

table3 enthält die Zuordnung, welche Einträge der table1 in der table3 zusammengefasst sind.

Bsp: table1 enthält Spieler, table2 enthält Vereine und table3 stellt dar, welcher Spieler in welchem Verein spielt.

Ich frage mich, warum diese Abfrage so lange dauert.

Klaus

[Bobby]

Moin

Ich frage mich, warum diese Abfrage so lange dauert.

Das kann verschiedene Ursachen haben, die du alle nacheinander mal checken solltest:

1. Zuerst einmal die Logik checken. (Da komm ich gleich noch drauf)
2. prüfen ob alles ordentlich indiziert ist und evtl eine Fremdschlüsselbeziehung existiert
3. Die Einstellun der DAenbank prüfen (key-Cache etc) Auch hier kann dies sehr unterschiedlich ausfallen

Die Anzahl der DAtensätze ist erstmal nicht der Grund. diese ist nicht all zu hoch und sollte für eine normale DB machbar sein (Du hast uns ja leider vorenthalten welches BDMS du nutzt)

Nun zur Logik

  
SELECT m.1,m.2,m.3  
FROM table1 m  
LEFT JOIN table2 rc ON rc.mID = m.mID  
LEFT JOIN table3 r ON rc.rID = r.rID  
WHERE m.5 != ''

Hier gibt es die erste ungereimtheit. Wenn Table 3 die Zuordnungstabelle ist, sollte diese entweder die Grundtabelle oder zumindest die 2. Tabelle sein die abgefragt wird. Den dann folgenden Teil kann ich nicht richtig nachvollziehen

AND  
m.mID NOT  
IN (  
SELECT mID  
FROM table2 WHERE mID IS NOT NULL  
)

Du fragst nochmals die Tabelle 2 (mit den Vereinen) und möchtest nicht, das die Id des Spielers = der ID des Vereines ist? Sorry, das kommt mir etwas unlogisch vor.

hab ich da was falsch verstanden?

Gruß Bobby

[Klaus]

Moin

Ich frage mich, warum diese Abfrage so lange dauert.

Hallo,

(Du hast uns ja leider vorenthalten welches BDMS du nutzt)

MySQL.

Hier gibt es die erste ungereimtheit. Wenn Table 3 die Zuordnungstabelle ist, sollte diese entweder die Grundtabelle oder zumindest die 2. Tabelle sein die abgefragt wird. Den dann folgenden Teil kann ich nicht richtig nachvollziehen

> > table3 enthält die Zuordnung, welche Einträge der table1 in der table3 zusammengefasst sind.

> > Bsp: table1 enthält Spieler, table2 enthält Vereine und table3 stellt dar, welcher Spieler in welchem Verein spielt.

Sorry, Satz1 enthält einen Flüchtigkeitsfehler, auf dessen Basis der Unsinn des 2. Satzes aufbaut ;-)

Nochmal:

table1 enthält (zum Beispiel) Spieler, table2 enthält die Vereine und table3 stellt die Zuordnung dar.

Übersetzt soll die Query im Grunde folgedes Ergebis generieren:

Zeige mir alle Spieler mit ein paar Daten (aus table1), die noch keinen Verein haben.

Der JOIN mit table2 ist zwar hier unnötig, aber wird in einer nachfolgenden Query gebraucht, in der ich die Spieler mit Verein suche, deren Verein aber eine Eigenschaft XY hat (was ich der Tabelle table2 entnehme).

Beide Queries verbinde ich dann über ein OR.

Klaus

[ms88]

> > Bsp: table1 enthält Spieler, table2 enthält Vereine und table3 stellt dar, welcher Spieler in welchem Verein spielt.

Hast du hier nicht eine 1:N Beziehung?
Also 1 Spieler spiellt in einem Verein, und in einem Verein spielen mehrere Spieler?

Falls ja, zieht man die ID auf die 1er Relation, d.h. die mittlere Tabelle entfällt komplett.

Du hättest somit:

Spieler --- spielt in --- Verein

Hättest du dann

Spieler mit einer Spalte Verein.id und dann Verein als 2. Tabelle.

z.B.:

Spieler mit den Feldern: id | Name | Adresse | gebDatum | Verein.id
Verein mit den Feldern:  id | Name | Ort

Dann hättest du mal 1 Join weniger, und wenn ein Spieler keine Zuordnung hat, ist Verein.id dort halt null.

Bin mir aber nicht sicher ob die Relation so korrekt ist, oder ob bei dir 1 Spieler auch in mehreren Vereinen spielen kann, dann brauchst du natürlich aufgrund N:N doch eine Zwischentabelle.

Wollt ich an der Stelle nur mal fragen.

lg

[ms88]

Oh das hast du am Ende gemeint mit der zusätzlichen Zuordnung, habs zu spät überissen.

[MudGuard]

Hi,

Hast du hier nicht eine 1:N Beziehung?

Nicht notwendigerweise.

Also 1 Spieler spiellt in einem Verein, und in einem Verein spielen mehrere

Spieler?

Spieler 1 spielt vom 01.01.2013 bis 02.08.2013 bei Verein 1.
Spieler 1 spielt seit 03.08.2013 bei Verein 2.

cu,
Andreas

[Tabellenkalk]

Hallo,

bis 02.08.2013 bei Verein 1.
seit 03.08.2013 bei Verein 2.

haben die keine Inkubationszeit?

Gruß
Kalk

[Tom]

Hello,

Spieler 1 spielt vom 01.01.2013 bis 02.08.2013 bei Verein 1.
Spieler 1 spielt seit 03.08.2013 bei Verein 2.

Drum denke immer daran, selbst in Schwarz-Weiß Darstellungen gibt es al dritte Möglichkeit immer noch Bunt, auch wenn das keiner vorhergeshen hat :-))

Liebe Grüße aus dem schönen Oberharz

Tom vom Berg

[bubble]

Hast du hier nicht eine 1:N Beziehung?

Nicht notwendigerweise.

Also 1 Spieler spiellt in einem Verein, und in einem Verein spielen mehrere
Spieler?

Spieler 1 spielt vom 01.01.2013 bis 02.08.2013 bei Verein 1.
Spieler 1 spielt seit 03.08.2013 bei Verein 2.

IMO besser die 1:n-Relation beizubehalten und für das Geschichtliche eine neue Tabelle zu nehmen.

Da dann vielleicht sogar nicht mit ID für den Verein, sondern direkt Name/Ort, sowas kann sich schließlich auch mal ändern.

MfG
bubble

[Tom]

Hello,

Spieler 1 spielt vom 01.01.2013 bis 02.08.2013 bei Verein 1.
Spieler 1 spielt seit 03.08.2013 bei Verein 2.

IMO besser die 1:n-Relation beizubehalten und für das Geschichtliche eine neue Tabelle zu nehmen.

Da dann vielleicht sogar nicht mit ID für den Verein, sondern direkt Name/Ort, sowas kann sich schließlich auch mal ändern.

Aber die von Andreas geschilderte Relation betrifft sogenannte "Bewegungsdaten" und nicht etwa "Stammdaten" oder "Historiedaten", also "Logbuch"

Die darf man eigentlich gar nicht ausblenden, wenn sie denkbar ist.

Liebe Grüße aus dem schönen Oberharz

Tom vom Berg

[bubble]

Aber die von Andreas geschilderte Relation betrifft sogenannte "Bewegungsdaten" und nicht etwa "Stammdaten" oder "Historiedaten", also "Logbuch"

Solch eine Konstellation der Daten hatte ich vor Augen:

Tabelle Spieler:  
ID int, Name varchar, Adresse varchar, VereinID int, ...  
  
Tabelle Verein:  
ID int, Name varchar, Ort varchar, ...  
  
Tabelle Historie:  
ID int, SpielerID int, VereinID int, Datum date, Aktion enum(in, out), ...  

Hierbei wären doch die Bewegungsdaten in Historie und mehr oder weniger die Spieler.VereinID und Spieler.Adresse (das sich der Rest ändert ist doch recht unwahrscheinlich). Der Rest wären die Stammdaten.

Das würde Andreas' Beispiel abdecken

Spieler 1 spielt vom 01.01.2013 bis 02.08.2013 bei Verein 1.
Spieler 1 spielt seit 03.08.2013 bei Verein 2.

und würde Klaus' Abfragen vereinfachen.

Die darf man eigentlich gar nicht ausblenden, wenn sie denkbar ist.

Ich versteh nicht was du damit meinst.

MfG
bubble

[Tom]

Hello,

Spieler 1 spielt vom 01.01.2013 bis 02.08.2013 bei Verein 1.
Spieler 1 spielt seit 03.08.2013 bei Verein 2.

IMO besser die 1:n-Relation beizubehalten und für das Geschichtliche eine neue Tabelle zu nehmen.

Da dann vielleicht sogar nicht mit ID für den Verein, sondern direkt Name/Ort, sowas kann sich schließlich auch mal ändern.

Aber die von Andreas geschilderte Relation betrifft sogenannte "Bewegungsdaten" und nicht etwa "Stammdaten" oder "Historiedaten", also "Logbuch"

Hier nochmal eine ganz angenehm lesbare Definition der Begriffe:

http://www.tu-chemnitz.de/wirtschaft/sapr3/dynamics/wbt/course/292/content.3613.8.1.html

Historie- und Bewegungsdaten klassifizieren dabei den gleichen Typ, nur mit dem Unterschied, dass sich Historiedaten nur auf die Meta-daten von "wer wann welche Operation" beziehen und die Bewegungsdaten auf die kaufmännischen Daten "wer wann was wieviel preis lieferzeit ..." beziehen.

Die darf man eigentlich gar nicht ausblenden, wenn sie denkbar ist.

Die m:n-Beziehung war gemeint

Wenn sie beim Entwurf des Datendesigns auch nur latent ins Hinterhirn rückt, dann sollte man die nach vorne holen und auf jeden Fall berücksichtigen. Anderenfalls sollte man sie als Softwareentwickler dediziert im Entwicklungsvertrag ausschließen :-O

Denn sie kommt zu geschätzten 1% der Fälle doch vor. Und das gibt dann mächtig Ärger!

Liebe Grüße aus dem schönen Oberharz

Tom vom Berg

[bubble]

Die m:n-Beziehung war gemeint

Zeige mir alle Spieler mit ein paar Daten (aus table1), die noch keinen Verein haben.
… nachfolgenden Query gebraucht, in der ich die Spieler mit Verein suche …

Klingt aber ganz stark nach 1:n-Beziehung.

Denn sie kommt zu geschätzten 1% der Fälle doch vor. Und das gibt dann mächtig Ärger!

Ja, das wäre ein arges Problem, das muss halt vorher definiert sein.

MfG
bubble

[Encoder]

Ich geh jetzt mal nicht auf den Inhalt an sich ein, das tun ja die anderen Beiträge schon, sondern nur auf das SQL.

Der JOIN mit table2 ist zwar hier unnötig, aber wird in einer nachfolgenden Query gebraucht

Das heißt was genau?

Beide Queries verbinde ich dann über ein OR.

Und das?

Du joinst table2 hinzu, mit der Bedingung table2.mID = table1.mID.
Daraus filterst du alles raus, wo table1.mID nicht in table2.mID enthalten ist. Das wäre doch einfacher mit einem INNER JOIN statt LEFT JOIN abgedeckt.
Vielleicht bremst die Subquery mit dem NOT IN das ganze aus. Hast du das schon weggelassen und geschaut wie lange es dann läuft?
Hast du Indexe/Primärschlüssel auf die ID definiert?

[Klaus]

Ich geh jetzt mal nicht auf den Inhalt an sich ein, das tun ja die anderen Beiträge schon, sondern nur auf das SQL.

Danke.

Der JOIN mit table2 ist zwar hier unnötig, aber wird in einer nachfolgenden Query gebraucht
Das heißt was genau?

Das war nur ein Hinweis, warum der LOIN überhaupt in meiner Query steht. Den könnte man getrost weg lassen.

Du joinst table2 hinzu, mit der Bedingung table2.mID = table1.mID.
Daraus filterst du alles raus, wo table1.mID nicht in table2.mID enthalten ist. Das wäre doch einfacher mit einem INNER JOIN statt LEFT JOIN abgedeckt.

Sehe ich nicht so.
Denn aus Tabelle 1 will ich alle Einträge haben, auch wenn sie in Tabelle 2 keinen "Relationalpartner" haben.
Die würde ich aber über den INNER JOIN mit heraus filtern.

Vielleicht bremst die Subquery mit dem NOT IN das ganze aus. Hast du das schon weggelassen und geschaut wie lange es dann läuft?

Stimmt. Genau diese Subquery ist das Nadelöhr.

Hast du Indexe/Primärschlüssel auf die ID definiert?

In Tabelle 1 und 2 sind die IDs primary Keys.

In Tabelle 3 liegt auf tabelle1_ID ein Index und Tabelle2_ID nicht.

Habe gerade versucht, auch auf Tabelle2_ID einen Index zu legen, aber das schafft phpmyadmin leider nicht. Vermutlich zu viele Einträge drin, aber anders kann ich auf die DB nicht zugreifen.

Klaus

[Encoder]

Denn aus Tabelle 1 will ich alle Einträge haben, auch wenn sie in Tabelle 2 keinen "Relationalpartner" haben.
Die würde ich aber über den INNER JOIN mit heraus filtern.

Stimmt. Aber mit deinem NOT IN in der WHERE Klausel filterst du sie ja dann doch noch heraus.

Stimmt. Genau diese Subquery ist das Nadelöhr.

Und genau diese Subquery könntest du ersetzen durch einen JOIN mit table2 mit der ID als Bedingung und WHERE table2.ID IS NULL. Aber wenn du das tust, filterst du dir nunmal genau die Einträge raus, die du mit dem INNER JOIN erst gar nicht hättest.

Oder täusch ich mich hier so sehr und hab nur nicht verstanden was passieren soll? Falls es so ist, klärt mich bitte auf.

Habe gerade versucht, auch auf Tabelle2_ID einen Index zu legen, aber das schafft phpmyadmin leider nicht. Vermutlich zu viele Einträge drin

Laut deine Eingangspost sind da 6500 Zeilen drin. Das ist nicht wirklich viel. Genau dieser Index wäre sehr wichtig. Wenns wirklich nicht gehen sollte, was du unbedingt nochmal untersuchen solltest, nenne table2 um, erzeuge eine neue mit Index, kopiere die Daten um und lösche dann die bisherige Tabelle.

[Klaus]

Hi,

Stimmt. Aber mit deinem NOT IN in der WHERE Klausel filterst du sie ja dann doch noch heraus.

Sehe ich immer noch nicht so.

Oder täusch ich mich hier so sehr und hab nur nicht verstanden was passieren soll? Falls es so ist, klärt mich bitte auf.

Ich vermute, das Problem ist noch nicht ganz deutlich geworden.

Tabelle 1: t1ID, Name, Datum, usw.
Tabelle 2: t2ID, Ort, usw.
Tabelle 3: t3ID, t1ID, t2ID

(Vereinfachte) Fragestellung: Suche alle t1ID, die nicht in Tabelle 3 enthalten sind.

Laut deine Eingangspost sind da 6500 Zeilen drin. Das ist nicht wirklich viel. Genau dieser Index wäre sehr wichtig.

Ohja, das ist er. Ich hatte heute früh nochmal in die Tabelle rein geschaut, da war der Index auf der Spalte plötzlich da. (Hat wohl etwas länger geduert??)

Und jetzt läuft die (gesamte) Query 0.7140 sek.

Damit kann ich ganz gut leben.

Gibts eigentlich irgendwelche Infos, wann in mySQL INTERSECT oder MINUS implementiert werden?

Klaus

[Karl]

Moin,

(Vereinfachte) Fragestellung: Suche alle t1ID, die nicht in Tabelle 3 enthalten sind.

na so wie du es schon gemacht hast, mit LEFT JOIN. Nur solltest du dann auch darauf eingehen und via IS NULL filtern.

Und jetzt läuft die (gesamte) Query 0.7140 sek.

das ist ja eine Ewigkeit, also für einen Computer.

[ms88]

SELECT m.1,m.2,m.3
FROM table1 m
LEFT JOIN table2 rc ON rc.mID = m.mID
LEFT JOIN table3 r ON rc.rID = r.rID
WHERE m.5 != '' AND
m.mID NOT
IN (
SELECT mID
FROM table2 WHERE mID IS NOT NULL
)

  
Könntest du statt der Subquery nicht direkt sagen:  
AND rc.mID IS NOT NULL  
  
Bin mir aber jetzt nicht ganz sicher ob das zum Gleichen Ergebnis führt.

[ms88]

SELECT m.1,m.2,m.3
FROM table1 m
LEFT JOIN table2 rc ON rc.mID = m.mID
LEFT JOIN table3 r ON rc.rID = r.rID
WHERE m.5 != '' AND
m.mID NOT
IN (
SELECT mID
FROM table2 WHERE mID IS NOT NULL
)

>   
  
Probier es mal mit not exists zb in die richtung:  
  
select m.1, m.2, m.3 from table1 m WHERE not exists (select \* from table2 WHERE mID=m.mID)