Hallo an alle SQL Jünger

ich möchte eine recht performante Suche für Bilder bauen.

Jeder Bilddatensatz hat ein Feld "keys" in dem kommasepariert bildbeschreibende Keywords als Liste Stehen.

Ich denke aber das Statement

select * from bilder
where keys LiKE 'form.suchbegriffe'

bei tausenden von Datensätzen eher nicht geeignet ist.
Hat mir da jemand ne "saubere" Lösung die auch nicht bei richtig vielen Datensätzen in die Knie geht?

Gruß Ronny

Die saubere Lösung wäre wohl eine Extra Tabelle mir den Keywords. Du müsstest dann nur das Keyword Feld parsen und daraus neue Datensätze erstellen. Ob sich das für "tausende" Datensätze lohnt ist aber eine andere Frage. Hast du den tatsächlich Performanceprobleme ?

Das

heißt, daß die Daten nicht in der ersten Normalform vorliegen.

Also muß jede Abfrage über alle Datensätze gehen, ist also nicht performant, weil immer ein Fullscan notwendig ist. Der Fullscan kann zwar über einen Index (und nicht über die Originalzeilen) gehen, die Indexstruktur kann aber nicht genutzt werden. Damit ist der Aufwand mindestens proportional zur Zahl der Datensätze - bei 100.000 Datensätzen ist das nicht mehr performant, weil auf jeden zugegriffen werden muß.

Bei weniger als vielleicht 5000 - 10.000 könnte man es zwar ausprobieren bzw. riskieren, wirklich Sinn macht das aber auch nicht.

Deshalb: Das

und das

schließt sich aus.

Die Lösung von Kabir ist doppelt gemoppelt: Erst nicht normalisierte Daten verwenden und dann zusätzlich eine normalisierte Tabelle erzeugen. Lieber gleich alles komplett normalisiert - und nur eine Logik nutzen. Bei sauber normalisierten Tabellen kann man mit einem Index auch bei mehreren 100.000 Datensätzen unproblematisch suchen.

Nein, ich habe verstanden das die Daten nicht normalisiert vorliegen. Was ich vorgeschlagen habe war eine Normalisierung.

soll heißen ich muss eine weitere Tabelle mit keywords anlegen, die Keyword und Bild-id beinhalten. Haben also 10 Bilder das gleiche Keyword müssen 10 Datensätz mit verschiedenen Bilder-ids erstellt werden.

Die Abfrage mit LIKE (über die keywordtabelle) bleibt aber damit bei Eingabe von "Auto" auch "Autoreifen" gefunden wird. Ist das richtig?

Gruß Ronny

Einem Bild können mehrere Keywords und einem Keyword mehrere Bilder zugewiesen werden.

Also hast Du eine m:n - Beziehung zwischen Bildern und Keywords.

Damit brauchst Du drei Tabellen: Eine für Bilder, eine für Keywords und eine ganz schmale, dreispaltige (Primärschlüssel, FK auf Bilder, FK auf Keywords) und sehr lange Verknüpfungstabelle (FK = Fremschlüssel, foreign key). Da sollten beide Spalten jeweils einen Ein-Spalten-Index bekommen, eventuell sogar mit der anderen Spalte als zusätzlicher Spalte (falls dies das DB-System zuläßt).

Die Suche per Like läuft über die Keywords-Tabelle (da ist die Spalte ebenfalls indiziert). Damit ist die Keywords-Tabelle sehr viel kleiner, weil jedes Keyword nur einmal vorkommt. Gleichzeitig werden über die Indices der Verknüpfungstabelle die Bilder hinreichend schnell gefunden.

Bei 10.000 Bildern und 150 Keywords läuft eben Like nur über 150 Werte, das ist lässig wenig. Und weil die Verknüpfungstabelle nur Integer-Spalten enthält, ist diese auch sehr klein, so daß wahrscheinlich die ganzen Indices im Arbeitsspeicher gehalten werden - auch wenn das 100.000 Kombinationen (zu jedem Bild 10 Keywords) sind.

OK
ich habe nun 3 Tabellen:

Tabelle1
id,keyword (z.b. "Auto")

Tabelle2
id,Keyword_id, Bilder_id

Tabelle3
id, Bilder_id, weitere Bilderdaten ...

Frage 1.
wie muss jetzt die Abfrage heißen um alle Bilddatensätze zu bekommen die mit dem Key "Auto" verknüpft sind?

Frage 2.
wie muss jetzt die Abfrage heißen um alle Bilddatensätze zu bekommen die mit dem Key "Auto" und/oder "Antenne" verknüpft sind?

Frage 3.
wie muss jetzt die Abfrage heißen um alle Keywords zu bekommen die mit der Bilder_id "4711" verknüpft sind?

Gruß Ronny

Misst, isch komme einfach nicht weiter mit dem Teil. Ich vesuche es mal mit InnerJoins.

Gruß Ronny

Wollt ich mich grade anmelden, um die Lösung zu skizzieren:

In solchen Fällen genügt es, nur über eine Where-Bedingung die Zugehörigkeit der Zeilen-ID zu einer ID-Liste zu überprüfen. Ein Join wäre zwar auch möglich, würde aber duplizierte Einträge erzeugen. Bei der Verwendung einer Unterabfrage in einer Where-Klausel sollte der Optimierer von sich her duplizierte Einträge ignorieren.

Damit löst diese Grundstruktur

alle drei Fragestellungen. Wenn nach zwei Begriffen gesucht wird, nimmt man ganz unten noch einen Or-Ausdruck mit dazu.

So, das Ganze noch in "Ronnyverständlich"

Select *
From Keywordtabelle
Where Keywordtabelle_id In

(Select Keywordtabelle_id
From Verknüpfungstabelle
Where Bildertabelle_id In

(Select Bildertabelle_id
From Bildertabelle_id
Where Keyword Like 'form.keyword')
)

Befindet ich mich gedanklich langsam auf der "Zielgeraden"?

Gruß Ronny

Im Prinzip ja.

Entscheidend dafür, daß was rauskommt (denn eigentlich müßte die obige Abfrage längst Daten liefern), ist aber natürlich, daß das Like auch noch ein '%' kriegt:

funktioniert, falls 'form.keyword' hinten ein '%' enthält. Ansonsten sollte man das noch hinten ranhängen:

Allerdings weiß ich aktuell nicht, ob der Sql-Code zusammengebaut bzw. wie zur Laufzeit dieser Ausdruck 'form.keyword' mit Inhalt gefüllt wird. Beim MS-SqlServer wäre das eine gespeicherte Prozedur der Rohform

Wenn in A.Keyword 'Auto' drinsteht und @suchkriterium mit 'A' belegt ist, dann sollte dieses Where-Kriterium mindestens die 'Auto'-Zeile zurückgeben.

Andersrum

wäre das falsch - weil 'A' nicht Like 'Auto%' ist.

das wäre dann eine query und pro record je eine subquery und pro subquery result record nochmal eine subquery.

wäre es nicht performanter das einfach mit joins zu machen?

select * from bildertable left join relationtable on (bild_id = relationtable.bild_id) left join keywordtable on (relationtable.keyword_id = keyword_id and keyword_name LIKE "auto%")

Nee. Beim Erstellen des Ausführungsplans dürfte das Db-System sehen, daß nur die innerste Unterabfrage variabel ist, von dieser also alles weitere abhängt.

Also wird zunächst die innerste Abfrage einmal ausgeführt, basierend auf dieser die mittlere, basierend auf dieser die äußere Abfrage. Ob die innerste Abfrage nur zeilenweise weiterverarbeitet wird oder ob erst das gesamte Ergebnis zwischengespeichert wird, dürfte von der Zahl der Datensätze und vielem mehr abhängen.

Sql ist eine Sprache der vierten Generation: Da sagt man nur, was man haben will - nicht wie. Den Rest sollte der Optimierer und der Generator für den Ausführungsplan erledigen. Wie eine so geschriebene Abfrage also ausgeführt wird, läßt sich nicht unbedingt aus der Struktur erschließen.

Theoretisch ginge das. Praktisch liefert das aber vielfach duplizierte Datensätze, da die Verknüpfungstabelle eine m:n-Beziehung abbildet. Die müßte man mit Distinct wieder entfernen. Bei der obigen Abfrage genügt es, daß die innerste Liste jede Id höchstens einmal zurückgibt. Bei so einem 'Where ID In (Unterabfrage)' 'weiß' dies aber der Optimierer - und dürfte ein Distinct bereits hier (plus bei der Zwischenabfrage) und dies effizienter als das klassische 'Distinct' machen.

In diesem Fall ist der Left Join falsch - weil ja nur Datensätze mit Übereinstimmung gesucht werden.

ja, hatte irgendwie inner join gedacht aber left geschrieben - der aussage, dass sql eine sprache ist mit der man sagt was man will - und nicht wie man will, kann ich nicht ganz beipflichten - schließlich muss ich ja auch die reihenfolge von combined indizies befolgen - da gibts auch noch einige weitere beispiele. jedenfalls würde ich mich da nicht immer 100% auf den query parser verlassen, wäre nicht das erste mal dass er etwas "anders" sieht, als "man es will"

Eine Abfrage über 3 Tabellen ist die eine Sache. Wie aber schreibe ich die neuen Keywords in die Verknüpfungstabelle wenn ich die ID in der Keywordtabelle noch gar nicht weiß?

Oder anders gefragt:
Wie kann ich die zu erwartenden ID eines Datensatzes ermittelns BEVOR ich den Datensatz schreibe?
Gibt es da einen eigenen Befehl oder muss ich mit MAX(keyword_id)+1 rumprobieren?

Gruß Ronny

Nein, es gibt einen Befelh um den Key des zuletzt angelegten Datensatzes auszulesen.
Ich finde ihn nur grade nicht. Sollte dir sonst auch keiner auskunft geben können poste ich ihn wenn ich wieder zu Hause bin.

Edit: Da ist er:
mysql_insert_id()

Sprich:
Eintrag in die Bildtabelle machen, ID merken.
Eintrag in die Keyworttabelle machen oder ID eines schon vorhandenen Keyworts lesen, ID merken.
Beide IDs in die Transfertabelle schreiben.

Ok spielen wir es nochmal durch:
bild bekommt ein schon bestehenden Keyword zugewiesen - ist klar

Bild bekommt ein neues Keyword:
Bild id merken
Keyword anlegen
Keyword Id ermitteln
Bild_id und Keyword_id in die Verknüpfungstabelle schreiben

Soweit kler

Wie aber frage ich per sql die neu entstandene Keyword ID ab?
Wie holt man sich mit "mysql_insert_id()" den Rückgabewert der eltzten Keyword_id?

Gruß Ronny

Ich verwende das zwar nicht.

Aber laut der mysql_insert_id()-PHP-Doku einfach durch den Aufruf dieser Funktion direkt nach dem Insert. Voraussetzung ist, daß die Tabelle ein AutoIncrement-Feld enthält.

Wenn die neue Bild-ID bekannt ist und das Keyword bereits existiert, dann kann man das Ermitteln der Keyword-ID und das Einfügen in die Verknüpfungstabelle auch in einem Durchgang erledigen:

Die Spalte 'Eintrag' sollte ohnehin eine Unique-Einschränkung erhalten, dann ist so etwas eindeutig.