Verhalten von "IN" in WHERE klausel / LEFT JOIN auch bei NULL ?

[MrSpoocy]

Hi,

ich habe da 2 bescheidene Fragen.
Zum 1nen, wenn man in der WHERE Klausel "abc IN (1,5,8)" macht, würde das für mich so aussehen als würde er die komplette tablle durchgehen und prüfen ob abc 1,5 oder 8 ist. Ich persönlich denke aber das MySQL das so optimiert das die Klausel das gleiche ist als würde man "WHERE abc = 1 OR abc = 5 OR abc = 8" schreiben. Zumal abc in Index Schlüssel ist.

Geh ich in der Annahme richtig ?
Kurz gesagt, geht er bei "IN" direkt auf die Index Schlüssel oder lädt er doch die ganze Tabelle ?

Meine 2te frage. Wenn ich "abc IN (1,5,7,5)" mache, wir der 2te "5" unterschlagen bei der Ausgabe, wie kann ich es machen das trotz doppelter Einträge bei der Ausgabe wirklich alle ausgegeben werden ?

Und meine 3te frage..

SELECT * FROm table AS t1  
LEFT JOIN table2 AS t2 ON t2.id=t1.ex1  
LEFT JOIN table2 AS t3 ON t3.id=t1.ex2  
LEFT JOIN table2 AS t4 ON t4.id=t1.ex3  
LEFT JOIN table2 AS t5 ON t5.id=t1.ex4  
WHERE CONCAT_WS(' ',t2.name,t3.name,t4.name,t5.name) LIKE '%bal%bla%'  

Wie ihr seht ist da schon mal 4x join auf 1ne Tabelle. Das lässt sich so erklären das tabele 1 4 Felder hat welche ID's zu "erweiterungen" enthalten. Und Ich brauche in der Abfrage halt alle Namen der Erweiterungen. Sodas das Auto dann z.b. "VW Max Power GT3" heißt weil Max, Power und GT3 sind in dem Fall 3 Erweiterungen.

Meine frage nun dazu ist, wenn t3.ex2 keinen wert hat, ob MySQL diesen LEFT JOIN dann ignoriert als würde er gar nicht da stehen ? Wenn nein, jemand eine Idee wie man das ändern kann oder vielleicht eine andere Art der Struktur der DB ?

mfg Spoocy

[Ilja]

yo,

ich habe da 2 bescheidene Fragen.

ich habe drei gezählt...

Ich persönlich denke aber das MySQL das so optimiert das die Klausel das gleiche ist als würde man "WHERE abc = 1 OR abc = 5 OR abc = 8" schreiben.

der IN Operator ist eine sehr bequeme kurzschreibweise einer logischen ODER operation. allerdings hat es noch einen kleinen sehr schönen nebeneffekt, es lößt die klammerproblematik, die bei der explizieten notation mit OR enstehen kann, wenn man auch das logische AND in der WHERE klausel verwendet. nicht vergssen AND bindet stärker als OR. insofern ließt sich IN nicht nur einfacher, es ist auch nicht so fehleranfällig.

Kurz gesagt, geht er bei "IN" direkt auf die Index Schlüssel oder lädt er doch die ganze Tabelle ?

bedinkt kann hier nur die antwort sein. wie eine SQL statement ausgeführt wird, das hängt von vielen faktoren ab, unter anderem auch welche inhalte die tabelle hat, wieiele datensätze in verhältnis dazu in die ergebnismenge kommen, welche version du erwendest, etc. was letztlich hilft ist ein blick auf den ausführungsplan. dort zeigt dir das dbms an, wie es gedenkt das  sql auszuführen.

Meine 2te frage. Wenn ich "abc IN (1,5,7,5)" mache, wir der 2te "5" unterschlagen bei der Ausgabe, wie kann ich es machen das trotz doppelter Einträge bei der Ausgabe wirklich alle ausgegeben werden ?

unterschlagen würde ich jetzt nicht sagen, aber letztlich macht es für das ergebnis keinen unterschied, ob die zweite 5 nun drinne steht oder nicht. es könnte aber eventuell den oben genannten ausführungsplan beeinflussen, vielleicht aber auch nicht. auch hier gilt, ausführungsplan anschauen. für das ergebnis ist es aber letztlich egal.

Wie ihr seht ist da schon mal 4x join auf 1ne Tabelle. Das lässt sich so erklären das tabele 1 4 Felder hat welche ID's zu "erweiterungen" enthalten. Und Ich brauche in der Abfrage halt alle Namen der Erweiterungen. Sodas das Auto dann z.b. "VW Max Power GT3" heißt weil Max, Power und GT3 sind in dem Fall 3 Erweiterungen.

die abfrage so wie sie jetzt ist kann so nicht funktionieren, da du spalten von den tabellen in der WHERE klausel benutzt, die über einen OUTER JOIN mit der ersten tabelle verbunden werden. das ergebnis wäre die gleiche menge, als wenn du einen INNER JOIN verwenden würdest. damit bedingungen in einem OUTER JOIN auch greifen, wenn sie nicht erfüllt sind, dazu müssen alle bedinungen nach der jeweiligen ON klausel stehen.

zum anderen musst du die tabelle nicht 4 mal aufführen, einmal würde reichen und dann in der ON klausel (wie gesagt nicht in der WHERE klausel) die verschiedenen fremdschlüssel mit dem logischen OR operator einbindest. Auch die LIKE Operation würde dort hin gehören.

zum anderen wirft die abfrage die frage nach dem daten-design auf. ich sage ganz bewußt nicht, dass es falsch ist, das kann man nicht ohne die jeweiligen umgebung zu kennen. aber sie wirft fragen auf. und da du ganz explizit nach einem anderen daten-design gefragt hast. und ja auf den ersten blick würde ich es anders machen. du könntest eine tabelle für alle möglichen erweiterungen machen, hast demzufolge also nur noch einen fremdschlüssel. dann brauchst du nur einen einfachen INNER JOIN der tabellen und könntest alle erweiterungen die es gibt mit der GROUP_CONCAT aggregatfunktion zusammenfassen. aber wie das daten-design genau aussehen sollte, das kann man nur genau sagen, wenn man deine umgebung genau kennt.

Ilja

[MudGuard]

Hi,

wie eine SQL statement ausgeführt wird, das hängt von vielen faktoren ab, unter anderem auch welche inhalte die tabelle hat, wieiele datensätze in verhältnis dazu in die ergebnismenge kommen,

Hm. Wie soll das bitte funktionieren?
Dazu müßte - um festzulegen, wie das Statement ausgeführt wird - bereits vor der Ausführung des Statements das Statement ausgeführt worden sein - denn sonst ist ja vor der Ausführung nicht bekannt, wieviele Datensätze in die Ergebnismenge kommen ...

cu,
Andreas

[Encoder]

Die Datenbank könnte sich bestimmte Statistiken halten, beispielsweise wie viele Einträge eine Tabelle hat und wie viele gleiche Werte in einem Index stehen usw. Anhand von sowas kann sie dann zur Laufzeit entscheiden, ob sie lieber gleich alles durchsucht, oder ob es sich lohnt den Index zu verwenden.

Die Art der Implementierung eines JOIN hängt (möglicherweise) von der Zeilenanzahl der beiden Eingabetabellen ab. Bei vielen Zeilen ist ein Ansatz sinnvoller, der erst mal etwas mehr Aufwand ist, der dann aber insgesamt doch schneller ist.

[Ilja]

yo,

Dazu müßte - um festzulegen, wie das Statement ausgeführt wird - bereits vor der Ausführung des Statements das Statement ausgeführt worden sein - denn sonst ist ja vor der Ausführung nicht bekannt, wieviele Datensätze in die Ergebnismenge kommen ...

eine falsche annahme. statistiken sind sicherlich ein weg, selbst optimierende dbms ein anderer. aber es gibt noch ein hilfsmittel, das ungemein wichtig für den optimierer ist, der aber nur sehr selten damit im zusammenhang gebracht wird, nämlich constraints. viele legen diese gar nicht an, weil sie der meinung sind, die daten wären auch so integer. aber sie erfüllen eben noch einen anderen zweck. nimme eine WHERE klausel, die einen wert einer spalte abfragt, wo ein UNIQUE constraint drauf liegt. diese informartion kann der optimierer sehr wohl verwenden und sollte es auch es auch tun.

Ilja

[MrSpoocy]

Ich zeige dir hier mal die Sturtur:

Tabelle "Item"

Item
iID | Name

Tabelle "Erweiterung"

eID | Name

Tabelle "Cross"

id | iID | eID1 | | eID2 | eID3 | eID4

Was ganau soll passieren ?
In der Cross tabelle sind die bis jetzt verfügbaren combinationen von Item & Erweiterung. Der user sucht nach "VW Max" nun setzte ich das ganze mit hilfe der vielen LEFT JOIN zusammen:

SELECT * FROM Cross AS T1
LEFT JOIN Erweiterung AS T2 ON T2.eID=T1.eID1
LEFT JOIN Erweiterung AS T3 ON T3.eID=T1.eID2
LEFT JOIN Erweiterung AS T4 ON T4.eID=T1.eID3
LEFT JOIN Erweiterung AS T5 ON T5.eID=T1.eID4
WHERE CONCAT_WS(' ',T1.Name,T2.Name,T3.Name,T4.Name,T5.Name) LIKE '%VW%Max%'

so ... nun wird das ganze aber schwerer, wenn nun z.b. eine erweiterung doppelt vorkommt bei 1nem Corss eintrag (ist absichtlich der fall das dieses möglich ist) dann würde das ergebniss nicht mehr "VW Max Max" sondern "WV Double Max" heißen und er muss dann auch danach suchen können. Also wenn der user "WV Dou Ma" eingibt muss er den eintrag finden.

Habe mir group_concat mal angeschaut und das sieht sehr vielversprechend aus, jedoch seh ich da keine möglichkeit mit dem "Double",  Triple, Quad. Man kann zwar in der gleichen abfrage ein COUNT einbauen und bekommt die anzahl, aber ich habe bis jetzt keine möglichkeit gefunden das dann irgendwie vor den group_concat string zu setzen.

Also sowas hier z.b.

SELECT GROUP_CONCAT(DISTINCT ELT(COUNT(Erweiterung ),'','Double ','Triple ','Quad '),Erweiterung SEPARATOR ' ') FROM ....

problem dabei macht das COUNT(Erweiterung), wenn ich anstelle dessen einfach mal eine zahl von 1-4 einsetze gibt er schon mal das richtige aus.

Ich würde mich über antwort von dir freuen oder vieleicht auch MSN kontakt dann kann ich die auch die genauen strukturen der Db zeigen.

mfg Spoocy

[Ilja]

yo,

In der Cross tabelle sind die bis jetzt verfügbaren combinationen von Item & Erweiterung.

auf den ersten blick sieht das anze wie eine typische n:m beziehung zwischen den tabellen item und erweiterung aus. insofern würde die cross tabelle (kein schöner name) jeweils zwei fremdschlüssel besitzen, einen von der item tabelle und einen von der erweiterungs tabelle. eventuell auch sinnvoll über beide fremdschlüssl ein UNIQUE constraint zu legen und die eigentschaft NOT NULL auf beide Fremdschlüssel.

so hast du immer mit 4 Fremdschlüssel von der tabelle erweiterung in cross zu kämpfen. ich kann den mehrwert davon nicht erkennen. was ds suchen betrifft, so bin ich aus deiner erläuterung noch nicht ganz schlau geworden.

Ich würde mich über antwort von dir freuen oder vieleicht auch MSN kontakt dann kann ich die auch die genauen strukturen der Db zeigen.

ich habe hier auf arbeit immer nur begrenzt zeit, aber wie ist den deine msn id ?

Ilja

[MrSpoocy]

Hab sie dir per e-mail geschrieben.

[Encoder]

Betrachten wirs mal nüchtern.

Kurz gesagt, geht er bei "IN" direkt auf die Index Schlüssel oder lädt er doch die ganze Tabelle ?

Die Frage ist bei OR doch die selbe. Auch hier könnte man denken dass die DB alles durchgeht und prüft. Oder halt auch nicht.
Wo siehst du da einen Unterschied?

Meine 2te frage. Wenn ich "abc IN (1,5,7,5)" mache, wir der 2te "5" unterschlagen bei der Ausgabe, wie kann ich es machen das trotz doppelter Einträge bei der Ausgabe wirklich alle ausgegeben werden ?

Versteh ich jetzt nicht. IN (...) gibt eine Menge an, in der ein Wert vorkommen soll.
Auch hier gibts keinen Unterschied zu Wert=1 OR Wert=5 OR Wert=7 OR Wert=5.
Meinst du dass die 5 mehrmals in der Tabelle steht und du kriegst nur eine dieser Zeilen zurück, weil nur ein Fünfer im IN steht? Da kommt schon alles was fünf ist.

Meine frage nun dazu ist, wenn t3.ex2 keinen wert hat, ob MySQL diesen LEFT JOIN dann ignoriert als würde er gar nicht da stehen ?

Das ist der Sinn vom LEFT JOIN. Die Zeile aus table kommt dann trotzdem, auch wenn es keinen Eintrag in der mit LEFT gejointen Tabelle gibt. Wie Ilja sagt, wenn du im WHERE auch noch irgendwas einschränkst, siehst natürlich wieder anders aus.