Abfrage

[gnarf]

Wie so oft schaff ich es nicht eine vernüftige MySql Abfrage hinzubekommen. Oder gibts da gar keine ? (Naja vermutlich schon, weil irgendwie kann man ja alles in sql machen, hab ich mir sagen lassen....) aber erst mal zum Problem:

Ich habe eine Tabelle. Diese speichert nur IDs (schlüssel). Konkret speichert es die IDs von Filmen und die Ids von Tags, die diese Filme beschreiben. Also z.b. Film Nr. 273 hat da Tag Nr. 837. usw.

Ich möchte jetzt folgende Abfrage machen:

Wenn ein Film das Tag X hat, welche Tags haben diese Filme dann wie häufig noch. Also zb. 20 Filme haben das Tag "Love". Ich möchte jetzt wissen welche Tags haben Filme mit tag "Love" noch so und wie häufig. Ausgegeben sollte also eine Liste werden wie:

15 Schnulze
12 Romanze
8 Hollywood
3 Schmus
1 Erotik

oder so.

Geht das ??????

(ob jetzt "Schnulze" oder die id von "schnulze" steht ist wurscht. Es geht also weniger um diesen join, als mehr um das prinzipielle problem)
zur lösung des problems braucht man also eigentlich nur die eine tabelle.

[Joachim]

Hi,

Wenn ein Film das Tag X hat, welche Tags haben diese Filme dann wie häufig noch.

sowas löst man mit joins. Hier gibts ein paar Beispiele:
http://www.little-idiot.de/mysql/mysql-118.html

Gruesse, Joachim

[gnarf]

Hallo !

Obwohl die Adresse deines Links ganz gut zu mir paßt ;-) glaube ich, dass du da falsch liegst. Die notwenidigen Informationen liegen alle in einer Tabelle, also brauche ich keine Joins. Aber auch eine kleine Join Nachhilfe schadet mir nicht ....

Danke & Liebe Grüße

Marcus

[Vinzenz Mai]

Hallo Marcus,

Obwohl die Adresse deines Links ganz gut zu mir paßt ;-) glaube ich, dass du da falsch liegst. Die notwenidigen Informationen liegen alle in einer Tabelle, also brauche ich keine Joins. Aber auch eine kleine Join Nachhilfe schadet mir nicht ....

doch, sowas nennt man einen Selfjoin. Damit könnte man Deine Problemstellung auch lösen :-)
In aller Unbescheidenheit halte ich die Datenbankartikel in SELFHTML aktuell für besser.

Freundliche Grüße

Vinzenz

[Vinzenz Mai]

Hallo,

Wie so oft schaff ich es nicht eine vernüftige MySql Abfrage hinzubekommen. Oder gibts da gar keine ?

diese Möglichkeit besteht. MySQL ist insbesondere in älteren Versionen sehr beschränkt. Und damit kommen wir zu einem entscheidenden Punkt, den Du beim Absenden Deines Postings vernachlässigt hast, obwohl Du darauf hingewiesen wurdest:

Welche Version von MySQL verwendest Du?

Ich habe eine Tabelle. Diese speichert nur IDs (schlüssel). Konkret speichert es die IDs von Filmen und die Ids von Tags, die diese Filme beschreiben. Also z.b. Film Nr. 273 hat da Tag Nr. 837. usw.

Ich möchte jetzt folgende Abfrage machen:

Wenn ein Film das Tag X hat, welche Tags haben diese Filme dann wie häufig noch. Also zb. 20 Filme haben das Tag "Love". Ich möchte jetzt wissen welche Tags haben Filme mit tag "Love" noch so und wie häufig.

Geht das ??????

Gib mir
    die Tag-ids,
    die Anzahl ihres Vorkommens
aus der Tag-zu-Film-Zuordnung
wobei nur die Filme berücksichtigt werden sollen
    die in der Liste der Film-ids enthalten sind,
        denen das Tag "Love" zugeordnet ist
gruppiert nach
    den unterschiedlichen Tag-ids
absteigend sortiert nach
    der Häufigkeit

einmal COUNT(), einmal IN-Operator, ein Subselect, GROUP BY, ORDER BY,
ab MySQL 4.1 aufwärts kannst Du obigen Pseudocode problemlos 1:1 umsetzen.

Freundliche Grüße

Vinzenz

[gnarf]

diese Möglichkeit besteht. MySQL ist insbesondere in älteren Versionen sehr beschränkt. Und damit kommen wir zu einem entscheidenden Punkt, den Du beim Absenden Deines Postings vernachlässigt hast, obwohl Du darauf hingewiesen wurdest:

da hst du recht. ich kann eine beliebige version installieren, deshalb hab ich es nicht erwähnt. das ist also kein problem. ich hab jetzt 5.0.41, also eh halbwegs aktuell.

Gib mir
    die Tag-ids,
    die Anzahl ihres Vorkommens
aus der Tag-zu-Film-Zuordnung
wobei nur die Filme berücksichtigt werden sollen
    die in der Liste der Film-ids enthalten sind,
        denen das Tag "Love" zugeordnet ist
gruppiert nach
    den unterschiedlichen Tag-ids
absteigend sortiert nach
    der Häufigkeit

einmal COUNT(), einmal IN-Operator, ein Subselect, GROUP BY, ORDER BY,
ab MySQL 4.1 aufwärts kannst Du obigen Pseudocode problemlos 1:1 umsetzen.

ich staune. und ich werde mal damit arbeiten. problemlos mag für manche gelten, aber nicht für mich ;-)

vielen, herzlichen dank & liebe grüße

marcus

[Vinzenz Mai]

Hallo Marcus,

Gib mir

-- Durch welches SQL-Schlüsselwort würdest Du diese Zeile ersetzen

die Tag-ids,

-- durch welche Spalte diese Zeile (das Komma bleibt erhalten :-))

die Anzahl ihres Vorkommens

-- welche Funktion angewandt auf welche Spalte

aus der Tag-zu-Film-Zuordnung

-- aus welcher Tabelle

wobei nur die Filme berücksichtigt werden sollen

-- welche Klausel ist für Einschränkungen da? Ersetze Filme durch Film-ID

die in der Liste der Film-ids enthalten sind,

-- Welcher Operator überprüft, ob ein Wert in einer Liste enthalten ist?
    -- Diese Liste bekommst Du über eine Abfrage, d.h. ein SELECT-Statement
    -- sowas nennt man ein Subselect

denen das Tag "Love" zugeordnet ist

-- welche Klausel war nochmal für Einschränkungen zuständig?
    -- Danach ist unsere Liste fertig, wir verlassen das Subselect, das
    -- übrigens in Klammern gepackt wird,

gruppiert nach

-- Welche Klausel ist für Gruppierungen zuständig?

den unterschiedlichen Tag-ids

-- nach welcher Spalte gruppierst Du

absteigend sortiert nach

-- Welche Klausel ist für das Sortieren zuständig,
    -- welches Schlüsselwort erreicht absteigendes Sortieren?

der Häufigkeit

-- nach welcher Spalte möchtest Du sortieren

einmal COUNT(), einmal IN-Operator, ein Subselect, GROUP BY, ORDER BY,
ab MySQL 4.1 aufwärts kannst Du obigen Pseudocode problemlos 1:1 umsetzen.

ich staune. und ich werde mal damit arbeiten. problemlos mag für manche gelten, aber nicht für mich ;-)

Also, es gilt einen Versuch! Poste, was Du zustandebekommen hast, und wo Du eventuell hängengeblieben bist.

Freundliche Grüße

Vinzenz

[gnarf]

Hallo nochmal,

Du hattest recht. Es ist halb so wild

SELECT * , COUNT( * )
FROM filmtag
WHERE film
IN (
  SELECT film
  FROM filmtag
  WHERE tag =179777
)
GROUP BY tag
ORDER BY COUNT( \* ) DESC

Jetzt aberdas Problem. Ich habe bisher das ganze in PHP gelöst. Also zuerst alle Filme suchen, die das jeweilige Tag haben. Dann alle Filme in einer Schleife durchgehen und jeweils die Tags in ein Array schreiben bzw. den Wert des Arrays um eins erhöhen.

Danach das Array sortieren und ausgeben.

Klingt unglaublich kompliziert (und ist es ja auch) und ich habe mir von einer SQL Lösung eine deutlich bessere Performance erwartet. Dazu muss gesagt werden, dass die Zuordnung derzeit ca 200.000 Einträge hat. Lustig an der Sache ist, dass die PHP Lösung ca 1 Sekunde braucht, die Verschachtelte SQL Abfrage aber 25 Sekunden .......

Hab ich einen Performancefehler in meiner Abfrage oder ist da einfach die händische Lösung besser. Schon erstaunlich. Macht nicht SQL intern genau das, was ich in PHP mache ????

marcus

[Vinzenz Mai]

Hallo Marcus,

Du hattest recht. Es ist halb so wild

leider wird Dein Statement maximal von MySQL akzeptiert.

  

> SELECT * , COUNT( * ) -- SELECT * ist böse  
> FROM `filmtag`  
> WHERE film  
> IN (  
>   SELECT film  
>   FROM filmtag  
>   WHERE tag =179777  
> )  
> GROUP BY tag          -- und in Verbindung mit GROUP BY falsch  
> ORDER BY `COUNT( * )` DESC  

Wenn Du GROUP BY verwendest, musst Du nach _allen_ Spalten, die Du auswählst, gruppieren, sofern keine Aggregatsfunktion verwendet wird.

Besser:

  
SELECT                    --  
    tag,                  -- wähle nur die Spalte aus, die Du benötigst  
    COUNT(tag) AS anzahl  -- gebe der Spalte einen netten Aliasnamen, macht  
                          -- sich auch in PHP besser  
FROM  
    filmtag  
WHERE  
    film  
IN (  
    SELECT  
        film  
    FROM  
        filmtag  
    WHERE  
        tag = 179777  
)  
GROUP BY  
    tag  
ORDER BY  
    COUNT(tag) DESC       -- in MySQL könntest Du auch den Aliasnamen nehmen  

Klingt unglaublich kompliziert (und ist es ja auch) und ich habe mir von einer SQL Lösung eine deutlich bessere Performance erwartet. Dazu muss gesagt werden, dass die Zuordnung derzeit ca 200.000 Einträge hat. Lustig an der Sache ist, dass die PHP Lösung ca 1 Sekunde braucht, die Verschachtelte SQL Abfrage aber 25 Sekunden .......

Hab ich einen Performancefehler in meiner Abfrage oder ist da einfach die händische Lösung besser. Schon erstaunlich. Macht nicht SQL intern genau das, was ich in PHP mache ????

Befrage MySL dazu, MySQL erklärt Dir gerne, was es solange macht - und es gibt auch ein Kapitel, das sich damit beschäftigt, wie Du Abfragen optimierst.

Eine Frage vornweg: Welche Indexe sind in Deiner Tabelle vorhanden?

Freundliche Grüße

Vinzenz

[gnarf]

Hallo Vinzenz !

Danke ! Du bist eine Wucht. Das  SELECT * nicht so optimal ist, war mir klar, aber die Faulheit ;-) Ich habs jetzt geändert. Nachdem ich aber nur 2 Spalten hab (Tag ID und Bild ID) wars nicht ganz so schlimm.

Indiziert habe ich beide Spalten.

Befrage MySL dazu, MySQL erklärt Dir gerne, was es solange macht - und es gibt auch ein Kapitel, das sich damit beschäftigt, wie Du Abfragen optimierst.

damit werd ich mich mal auseinandersetzen und auch mit den von dir empfohlenen datenbankartikeln auf dieser seite. dann meld ich mich wieder !

liebe grüße

marcus