[MySQL] WHERE Clause bei Join

[progfrog]

Moin Community,
bin hier schon eine ganze Weile am Grübeln, mit fällt aber keine Lösung ein.

Folgendes:
Ich habe einen Query der mir über einen Join das untenstehende Result auswirft. (Hoffe mal man kann mein ASCII Gekrüppel lesen^^)

Ich möchte aber nicht das unten stehende Result haben, sondern nur die icons, die sowohl Eigenschaft 3 als auch Eigenschaft 6 aufweisen.

Ich brauche also einen Where Clause der das tut. Oder gibt es nicht noch andere MySQL Funktionen für sowas? Würde das gerne mit nem MySQL Query lösen, da das erstens schneller sein dürfte und zweitens mein PHP Code nicht so verunstaltet wird

Wäre über eine schnelle Lösung sehr dankbar!

Vielen Dank schonmal im Vorraus
Gruß progfrog


Ich habe die hier angegeben Informationen so wenig wie nötig gehalten, damit mein Beitrag nicht wieder so lang wird. Wenn du noch was über meine DB Struktur oder den Query wissen müsstest frage gerne. Sollte aber so reichen, denke ich.

(In diesem Fall steht Eigenschaft 6 für blau und 3 für Viereckig. Ich möchte also nur blaue vierecke im ergebnis sehen

icon -------------- | eigenschaft_id
====================+===============
blender.png ------- | 6
firefox-icon.png -- | 6
icon-OfficeWord.png | 6
itunes_icon.png --- | 6
photoshopiconbd9.png| 3
photoshopiconbd9.png| 6
quickTime_icon.png -| 6
safari-icon.png ----| 6
thunderbird-icon.png| 6
WinZip.png -------- | 6
WinRar.png -------- | 6
WLM.png ----------- | 6
120px-Audacity.png | 6

 

[F0RC3]

AW: [MySQL] WHERE Clause bei Join

Hallo,

vielleicht kann ich dir helfen, aber ich würde gerne Tabellen Struktur und dein Query sehen.

 

[kleinerOnkel]

AW: [MySQL] WHERE Clause bei Join

lies mal nach: concat()

 

[progfrog]

AW: [MySQL] WHERE Clause bei Join

@kleinerOnkel:
CONCAT() Dient doch lediglich dazu zwei Strings im SELECT ananeinander zu hängen. Oder unterliege ich da einem Irrglauben?

Beipiel könnte ausgeben "Das tolle Icon Photoshop ist in der Datenbank vorhanden."
SELECT
CONCAT("Das tolle Icon ", icon_name, " ist in der Datenbank vorhanden.")
FROM ...

@FORC3:
Hier wäre mal meine Struktur und mein aktueller Query. Ich will wie gesagt erreichen, dass ich nur die Icons bekomme, die Eigenschaft 3 UND 6 auffweisen. In diesem Falle PS.

 Tabelle: icons
================
 Feld | Typ          | Attribute         | Extra          |
------+--------------+-------------------+----------------+
  id  | int(10)      | UNSIGNED          | auto_increment | PRIMARY KEY
  url | varchar(255) | utf8_general_ci |                |
  

 Tabelle: icons_attributes
==========================
 Feld         | Typ          | Attribute | Extra |
--------------+--------------+-----------+-------+
 icon_id      | int(10)      | UNSIGNED  |       | PRIMARY KEY  
 attribute_id | smallint(5)  | UNSIGNED  |       | PRIMARY KEY



Momentaner Query:
  
  SELECT
    url,
    attribute_id
  
  FROM
    icons
          INNER JOIN icons_attributes
             ON icons.id = icon_id

  WHERE
    attribute_id = 6  OR attribute_id = 3

 

[kleinerOnkel]

AW: [MySQL] WHERE Clause bei Join

und warum schreibst du dann ne query mit or (oder) wenn Du ein AND (und) haben willst

 

[progfrog]

AW: [MySQL] WHERE Clause bei Join

Nunja, würde ich das so schreiben ginge das ja irgendwie nicht.
attribute_id = 6 AND attribute_id = 3
Das wäre als ob ich den Fall abfrage, dass 1 == 1 AND 1 == 2. Nonsens. xD

Die Sache ist ja, dass die beiden Attribute ja je in einzelnen Datensätzen vorkommen.

IconA | 6
IconA | 3
IconB | 3

In solch einem Fall möchte ich eben IconA als Ergebnis haben, weil es einen Datensatz gibt, der MeinIcon die Eigenschaft 6 zuweist, und weiter einen der ihm die Eigenschaft 3 zuweist. IconB hingegen, weist nur Eigenschaft 3 auf. Verstehst du was ich meine?

 

[cebito]

AW: [MySQL] WHERE Clause bei Join

Die Sache ist ja, dass die beiden Attribute ja je in einzelnen Datensätzen vorkommen.

Da frag ich mich, warum du nicht gleich mehrere Attribute dem Icon in einem Datensatz zuweist? Deine DB bedarf einer Überarbeitung, dort liegt wohl das Problem.

icon-id - zahl
form - eckig
farbe - blau
art - werkzeug
url - photoshopiconbd9.png usw...

Dann kannst du auch form = 'eckig' AND farbe = 'blau' usw. abfragen...

 

[F0RC3]

AW: [MySQL] WHERE Clause bei Join

Die Tabellenstruktur ist nicht optimal. Ich sehen das ähnlich wie cebito, allerdings würde ich etwas mehr normalisieren. Hängt aber von den Datenmengen, Abfragen etc. ab.

mag sein, dass du "irgendwie" ein Query schreiben kannst, dass dir die Daten so liefert wie du es brauchst (ich wüsste auf Anhieb gerade nicht)

Eine Umstrukturierung der Daten / Tabellen würde das ganze auf jeden Fall vereinfachen.

Du könntest auch folgendes machen:

Tabelle Icons:
id INT
url varchar
form INT
farbe INT

Tabelle Icons attributes:
ia_id INT
ia_eigenschaft varchar (hier würde dann "blau", "eckig", "rund" stehen

das Ganze sieht dann bei mir so aus, gibt vielleicht aber auch eine einfachere Lösung.

SELECT i.url, i.form, i.farbe
FROM icons AS i
JOIN icons_attributes AS ia ON i.form = ia.ia_id
JOIN icons_attributes AS ia2 ON i.farbe = ia2.ia_id
WHERE i.form = 3 AND i.farbe = 6

 

[Darweis]

AW: [MySQL] WHERE Clause bei Join

Da du keine Aussage gemacht hast ob du die Datenbankstruktur NICHT ändern kannst würde ich auch zu einer Überarbeitung raten.

Stichwort NORMALISIERUNG:

Sql lernen - ein Tutorial zum Selbststudium mit einer Beispiel-Datenbank

Punkt 8 oder 9 links im Menu geht es los.

 

[progfrog]

AW: [MySQL] WHERE Clause bei Join

Danke für die vielen Antworten. Und erstmal, ja ich weiß schon wie ich eine Datenbank normalisiere. Ist vieleicht in meinem ersten Posting nicht klar geworden. Habe versucht das Problem so einfach wie möglich zu halten. Trotzdem danke für den Link zum SQL Tutorial.

Warum ich meine Datenbank so angelegt habe wie sie ist:
Belehrt mich bitte eines Besseren wenn ich hier falsch liege.

Die Sache ist die, dass ein Icon beliebig viele Eigenschaften aufweisen kann. Es kann als Form wie beispielsweise das Winamp Icon eine Raute und einen Blitz enthalten. Weiter kann es auch diverse Farben haben. Um mal beim Winamp Icon zu bleiben, orange und weiss. Zusätzlichlich dazu kann das Icon noch andere Bildliche Attribute enthalten. Beispielsweise im iTunes Icon die Cd, oder bei iCal der Kalender.

Diese Beziehungen galt es in einer Datenbank darzustellen. Da beliebig viele Eigenschaften jedes Typs vorhanden sein können, muss ja eine m:n Beziehung gelten.
Also brauche ich vorerst schonmal diese Verknüpfungstabelle mit zwei Primary Keys. Da die Eigenschaften wie Form, Farbe etc. sich eigentlich alle als Attribute fassen lassen, habe ich diese in einen Topf geworfen um die Datenbank nicht unnütz zu verkomplizieren.

Der Query den ich versuche zu bauen, dient ja einer Suchfunktion. Der User gibt eine Suchanfrage ein, und daraus werden dann die darin enthaltenen Attribute ausgelesen.
Das können ja dann logischerweise beliebig viele sein. Um das Beispiel einfacher zu halten, bin ich hier im Thread erstmal von zwei Attributen ausgegangen. Es kann natürlich aber auch sein, dass der User nach "Roter Kasten mit grünem Blitz und Note" sucht. (kenne zwar kein solches Icon ...) In dem Fall hätte ich ja 5 Attribute, die ein Icon zu erfüllen hat, damit der Query es auswerfen soll.


Habe mich jetzt nochmal bisschen durch die MySQL Doku gewühlt aber auch keine passenden Funktionen gefunden. Ich denke ich sollte das ganze per PHP sortieren.

Oder seht ihr meine Datenbankstruktur auch nach meiner Erklärung noch als suboptimal an? Wenn ja nur her mit der Kritik!

Lieben Gruß
progfrog

 

[F0RC3]

AW: [MySQL] WHERE Clause bei Join

OK

das ändert die Sache etwas und meine Struktur von oben funktioniert so nicht...

Dennoch halte ich deine Struktur für nicht optimal und daher werde ich dir einen Vorschlag unterbreiten:

Tabelle1 (Icons):
id
url
=> 1 photoshopiconbd9.png


Tabelle2 (Attribute):
id
eigenschaft (blau, eckig, whatever)
=>
1 blau
2 eckig
3 rund

Tabelle3 (Zuordnung):
id
url_id
eigenschaft_id
=>
1 1 2
2 1 1

Alternativ (ist ja immer alles von diversen Faktoren abhängig (Datenmengen, was möchte ich mit den Daten machen?)

Tabelle1 (Icons)
id
url
attributes (s e r i a l isiert, oder Eigenschaften durch Trennzeichen getrennt (entweder Klartext oder IDs aus 2ter Tabelle)

Tabelle2 (Attribute)
id
eigenschaft

Das also mal 2 Vorschläge. In welchen Dimensionen muss ich mir das ganze vorstellen? Ab gewissen Größen von Datenbanken ist Normalisierung nicht mehr praktikabel da man 2 oder mehr Tabellen mit Hunderttausenden oder gar Millionen von Einträgen nicht unbedingt joinen sollte.

 

[progfrog]

AW: [MySQL] WHERE Clause bei Join

Moin,

also dein erster Vorschlag entspricht ja insofern etwa meiner Version. Ich habe ja schließlich auch noch eine Tabelle in der die Attributes ihren Attribute_ids zugeordnet werden.

Die Sache mit der Zweiten Version ist auch ne gute Idee. Ich werde heute Abend mal ausführlich drüber nachdenken. Hab grad nur kurz Zeit.

Bezüglich den Dimensionen der Datenbank:
Momentan, also in der Entwicklungsphase, spielt sich das ganze noch im Bereich von ca. 100 Datensätzen ab. Wenn das ganze aber erstmal im Einsatz ist ist es schon denkbar, dass die Datenbank vieleicht bis zu 10.000 Icons enthalten wird. In die Hunderttausend oder gar Millionen wird es wohl nicht gehen.

Über die Tatsache, dass es nicht so ratsam ist, solch große Tabellen zu Joinen war ich mir noch garnicht so bewusst.

Vielen Dank dir für den Tip mit den Joins und den Denkanstoß bezüglich deines zweiten Vorschlages. Ich werd halt heute mal drüber nachdenken und dann nochmal posten wie ich's letztlich gelöst habe.

Thx @ all.

Gruß progfrog

 

[progfrog]

AW: [MySQL] WHERE Clause bei Join

Sodele habe mal drüber nachgedacht und beschlossen beide Versionen einmal auszuprobieren. Ich habe deine Version nun so umgesetzt, dass in der Icon Tabelle eine VARCHAR Spalte ist, in der dann eine Kommagetrennte Liste der Attribut_ids steht. Diese wird dann im Query mit: "attribut_id LIKE "%,...,%" durchgefragt.
Habe ein Benchmark laufen lassen und bin zu dem Schluss gekommen, dass die von dir (FORC3) als zweite Variante gepostete Version deutlich schneller ist als meine aktuelle.

Die Daten beziehen sich auf eine Datenbank mit ca. 21 Icons und 50 Verschiedenen Eigenschaften. Entsprechend war meine alte Verknüpfungstabelle etwa 100 Datensätze lang.

Dann sage ich mal vielen Dank für die Mithilfe und freue mich, dass ich meine Suchgeschwindigkeit gerade mit mehr Keywords knapp verdoppeln konnte. Man muss natürlich schauen wie sich das entwickelt wenn in der Icontabelle auf einmal 1.000 Icons statt 20 enthalten sind. Aber ich werde denke ich trotzdem erstmal dabei bleiben.

Vielen Dank!!!

Gruß progfrog



Hier die Ergebnisse: (mit deiner DB Struktur meine ich die zweite Version von FORC3).
________________________________________________________________
=====================================
<< DB Struktur Vergleich - Benchmark >>
=====================================


Durchschnitt von 100.000 Suchdurchläufen bei 1 Keyword
=========================================================
* 1 Durchlauf mit meiner DB Struktur: 0,664ms
* 1 Durchlauf mit deiner DB Struktur: 0.587ms

Durchschnitt von 100.000 Suchdurchläufen bei 3 Keywords
=========================================================
* 1 Durchlauf mit meiner DB Struktur: 0,834ms
* 1 Durchlauf mit deiner DB Struktur: 0,398ms

Durchschnitt von 100.000 Suchdurchläufen bei 6 Keywords
=========================================================
* 1 Durchlauf mit meiner DB Struktur: 1,077ms
* 1 Durchlauf mit deiner DB Struktur: 0,543ms

 

[F0RC3]

AW: [MySQL] WHERE Clause bei Join

das freut mich zu hören
es kann aber sein, dass dein LIKE query bei sehr vielen Datensätzen zu langsam wird.
man sollte kein Scheu davor haben PHP die Arbeit machen zu lassen denn wenn die Datenbank mit einem Query gelocked ist hält das alle andere Abfragen auf. Ob du in diese Regionen kommst kann ich natürlich nicht vorhersagen.

Not macht erfinderisch, soviel kann ich sagen

Ich arbeite täglich mit MySQL Datenbanken die mehrere GB groß sind und mehrere Millionen Datensätze enthalten.
Da muss man dann eben manchmal auch zu ungewöhnlichen Methoden greifen und von bestimmten Standards abweichen.

Wenn ich das Thema interessiert kann ich dir sehr das Buch empfehlen.

 

[progfrog]

AW: [MySQL] WHERE Clause bei Join

Dank dir für die Buchempfehlung. Ich habe zwar momentan aus Urlaubstechnischen Gründen keine 50€ über, aber interessieren tut es mich schon.

man sollte kein Scheu davor haben PHP die Arbeit machen zu lassen

Da bin ich mir eben nicht so sicher, da mein voriger OR Query ja mit mehr Datensätzen in der Datenbank auch ca. 1.000 Zeilige Arrays ausspucken würde. Weiß nicht wie PHP das dann findet wenn man da einmal komplett drüber iteriert

btw. Wo arbeitest du, wenn deine Tabllen mehrere GB groß sind? Das ist voll viel!

Gruß progfrog

 

[F0RC3]

AW: [MySQL] WHERE Clause bei Join

Sorry aber ich werde dir nicht sagen wo ich arbeite. :nee:

unsere Datenbanken sind 173GB und 60GB groß Tabellen sind sowohl MyISAM als auch InnoDB. Das ganze natürlich nicht auf einem Server.

Um die Datenbank Last in Grenzen zu halten werden diverse Sachen gecached, das meiste in memcached.

Die Sache mit PHP muss man halt im Einzelfall abschätzen ob es sinnvoller ist die Datenbank mit einem Query zu blockieren dass mehrere Sekunden dauert oder ob PHP etwas Zeit braucht um die Daten aufzubereiten und dabei evtl. den Webserver belastet.
Prinziell kann man PHP aber schon einiges an Arbeit aufhalsen bevor es wirklich langsam wird.
Wie gesagt, Einzelfall-Entscheidung und von diversen Faktoren abhängig.