Hallo,

ich komme mit einer mySQL-Abfrage nicht weiter, vielleicht könnt ihr mir einen Tipp geben, was ich falsch mache.

Aufgabe: ich sammle Musik-Gruppen aus einer Tabelle und die jeweils dazu hinterlegten Musik-Genres aus einer zweiten Tabelle. Die einzelnen Genres jeder Band werden durch das GROUP_CONCAT als Komma separierte Liste angezeigt.

Ausgabe:
http://vampster.com/genres/example_genres3.php

Die dazugehörige Abfrage:
SELECT name, band_id, tag, naehe,GROUP_CONCAT(tag ORDER BY tag)
FROM bands_tags LEFT JOIN bands_master ON bands_tags.band_id = id
GROUP BY name ORDER BY name ASC,naehe DESC

(Bezeichnungen:
name: Bandname
band_id: Band ID
tag: Genre-Bezeichnung
naehe: Gewichtung -> 10 = niedrige Übereinstimmung von Band zu Genre, 100 = hohe Übereinstimmung.

aus der tabelle bands_master und dem LEFT JOIN hole ich den bandnamen passend zur band_id.)


Nun möchte ich aber gerne nach den Genres Filtern. Mein Ansatz geht leider nicht:

2. Abfrage:


if (isset($genre)) {
$query = "SELECT name, band_id, tag, naehe,GROUP_CONCAT(tag ORDER BY tag)
FROM bands_tags LEFT JOIN bands_master ON bands_tags.band_id = id
WHERE tag =\"".$genre."\" GROUP BY name
ORDER BY name ASC,naehe DESC;";
}

Ausgabe (Beispiel):
http://vampster.com/genres/example_genres3.php?genre=alternative%20rock

Es wird also nur das eine Genre angezeigt, nach dem ich in der URL filtere. Mein Ziel wäre, auch die anderen Genres angezeigt zu bekommen, wenn zur Band weitere Einträge vorhanden sind.

Kann mir jemand helfen?

Vielen Dank schonmal,
Markus

PS: Der Server läuft noch mit mySQL 4.1.25, falls das wichtig ist.

WHERE tag =\"".$genre."\" group by name


Wenn ich das so lese, kann das ja auch nicht wirklich gehen. Du sagst ja der DB das er nur die Datensätze haben soll, welche dieses Tag beinhalten. woher soll die DB denn wissen das du dennoch mehr willst. Oder habe ich deinen Post falsch interpretiert ?

Hallo,

kannst du bitte den Code mit den entsprechenden Tags formatieren und die benutzten Tabellen- und Spaltennamen erläutern? Dann kann man dir gezielter helfen.

Gruß,

Amica

Dreh mal dein Error Reporting auf. Das hier ist nämlich Käse:
$genre = $_GET["genre"];
if (isset($genre)) {
// ...
}

Wenn ich das so lese, kann das ja auch nicht wirklich gehen. Du sagst ja der DB das er nur die Datensätze haben soll, welche dieses Tag beinhalten. woher soll die DB denn wissen das du dennoch mehr willst. Oder habe ich deinen Post falsch interpretiert ?

ich bin davon ausgegangen, dass ich das mit dem
GROUP_CONCAT(tag ORDER BY tag) erreiche. Der Gedanke war, suche alle Eintrage, die das Genre "$genre" beeinhalten und zeige dann noch die weiteren Genres zu dieser Band. Habe das GROUP_CONCAT bisher noch nie verwendet, verwende ich es falsch?

Hallo,

kannst du bitte den Code mit den entsprechenden Tags formatieren und die benutzten Tabellen- und Spaltennamen erläutern? Dann kann man dir gezielter helfen.

Gruß,

Amica


Ok, so?

Gruß,
Markus

Ok, so?

Jep, danke.

Da musst du wohl nochmal auf bands_tags joinen, also einmal bands_tags verwenden, um das Ergebnis einzugrenzen, darüber auf die Bands joinen und von dort nochmal auf bands_tags (mit Alias), um darauf das group_concat anzuwenden.

Wenn du nur einmal bands_tags verwendest, wird es durch die Where-Klausel beschränkt, wie /(ron)+3005/ schon sagte.

Gruß,

Amica

Danke schön! Werde das morgen früh mal ausprobieren.
Dann mal schauen, ob ich es kapiert habe :)

Gruß,
Markus

Hallo Amica,

ich muss nochmal nachfragen, weil ich nicht weiter komme: Die WHERE-Klausel fliegt raus, und dafür kommt ein zweiter JOIN rein, der die zusätzlichen Genres abfragt?
Steh wohl auf dem Schlauch.

Danke & Gruß,
Markus

Nein, die Where-Klausel bleibt und filtert immer noch das Genre raus, nach dem gesucht werden soll. Sonst würde das ja an der Aufgabenstellung vorbei gehen.

Hier mal ein Beispiel:


select
person.first_name,
person.last_name,
phone2.country_code,
phone2.area_code,
phone2.number,
phone2.extension
from phone as phone1
join person on person.id = phone1.person_id
join phone as phone2 on phone2.person_id = person.id
where phone1.country_code = 49
and phone1.area_code = 172
and phone1.number = 1234567
and phone1.extension is null


Findet alle Telefonnummern der Person, welche auch die Telefonnummer +49 172 1234567 besitzt. Über phone1 wird nur gefiltert und die Person rausgesucht. Die Telefonnummern im Ergebnis stammen dagegen aus phone2.

Nein, die Where-Klausel bleibt und filtert immer noch das Genre raus, nach dem gesucht werden soll. Sonst würde das ja an der Aufgabenstellung vorbei gehen.

Okay, danke, probier ich so nochmal - und sorry für meine vielleicht 'dummen' Fragen. Ich kratze programmiertechnisch noch ziemlich an der Oberfläche und versuche, so gut ich eben kann, solche Dinge selbst zu programmieren. Aber an dem Punkt komm ich gerade alleine einfach nicht weiter.

Gruß,
Markus

so wie ich es verstanden habe, sollte die Abfrage etwa so aussehen?


SELECT name, band_id, tag, naehe,alle_genres.tag, GROUP_CONCAT(tag order by tag) FROM bands_tags

JOIN bands_master ON bands_tags.band_id = id
JOIN bands_tags AS alle_genres ON alle_genres.tag = bands_tags.tag

where tag = "alternative rock"
group by name
ORDER BY name ASC,naehe DESC


Fehlermeldung: "#1052 - Column 'band_id' in field list is ambiguous".

Wieso ist die Band ID jetzt mehrdeutig?

Wieso ist die Band ID jetzt mehrdeutig?

Na weil sie einmal aus bands_tags und einmal aus alle_genres kommen kann. Benutz doch einfach qualifizierende Spaltenreferenzen wie in meinem Beispiel (schreib den Tabellennamen/Alias davor).

Na weil sie einmal aus bands_tags und einmal aus alle_genres kommen kann. Benutz doch einfach qualifizierende Spaltenreferenzen wie in meinem Beispiel (schreib den Tabellennamen/Alias davor).


jep, okay. die abfrage geht jetzt prinzipiell, ist aber noch falsch.

SELECT bands_master.name, bands_tags.band_id, bands_tags.tag, bands_tags.naehe, alle_genres.tag, GROUP_CONCAT( bands_tags.tag
ORDER BY bands_tags.tag )
FROM bands_tags
JOIN bands_master ON bands_tags.band_id = bands_master.id
JOIN bands_tags AS alle_genres ON alle_genres.tag = bands_tags.tag
WHERE bands_tags.tag = "modern metal"
GROUP BY name
ORDER BY name ASC , naehe DESC

Ausgabe am Beispiel "Modern Metal":
http://vampster.com/genres/example_genres4.php?genre=modern%20metal

Diesem Genre wurden erst fünf Bands zugeordnet. In der Ausgabe erhalte ich fünf mal den Begriff "modern metal" aufgezählt. Dann ist der zweite JOIN vermutlich noch falsch?

Ich glaube, das ist mir alles doch eine Nummer zu hoch.

Du brauchst nur bands_tags.* im Select-Teil wegzulassen. Und im group_concat musst du natürlich alle_genres verwenden.

Du brauchst nur bands_tags.* im Select-Teil wegzulassen. Und im group_concat musst du natürlich alle_genres verwenden.


Hab das soweit versucht, mit folgendem Ergebnis:

http://vampster.com/abfrage1.jpg

Erstens: Der JOIN ist Blödsinn, ich sehe es aber jetzt erst:
ON alle_genres.tag = bands_tags.tag
Das liefert dir doch genau dieselben. Es soll ja aber die liefern, die zur selben Band gehören. Also musst du über die Spalte band_id joinen, nicht über tag.

Zweitens: GROUP_CONCAT(DISTINCT ...)
Damit erscheint jedes Genre nur einmal, auch wenn es dann vermutlich sowieso nur einmal auftauchen würde, aber ich kenne ja nicht deine komplette DB und deine späteren Abfrageabsichten. Also schadet es auch nicht.

Drittens: Sieh dir das Telefonnummern-Beispiel nochmal ganz in Ruhe und ganz genau an, bis du es vollständig verstanden hast. Deine Herangehensweise ist noch etwas zu wild und zeugt von "Probieren über Studieren", was im DB-Umfeld nicht unbedingt zielführend ist.

Ja, vielen vielen Dank soweit, ich sollte mir das mal in Ruhe anschauen und auch richtig verstehen.

Mein Problem ist einfach, dass wir für unser Magazin derzeit keinen Programmierer haben und ich das umzusetzen versuche, was ich kann. So ist halt momentan die Situation, auch wenn sie nicht befriedigend ist.

Supi, jetzt geht's:

http://vampster.com/genres/example_genres5.php?genre=modern%20metal
Das klappt gut, da habe ich nur 5 Bands mit 2 Genres im Schnitt.


Bei Dark Wave als Genre wird's schon holpriger:
http://vampster.com/genres/example_genres5.php?genre=dark%20wave

54 passende Bands im Archiv -> Query braucht über 20 Sekunden.

Bei anderen Genres, zu denen wir richtig viele Bands haben, läuft die Abfrage ins Nirwana.

Die Abfrage dazu sieht so aus:
SELECT bands_master.name, bands_tags.band_id, bands_tags.naehe,
GROUP_CONCAT( " ", alle_genres.tag )
FROM bands_tags
JOIN bands_master ON bands_master.id = bands_tags.band_id
JOIN bands_tags AS alle_genres ON alle_genres.band_id = bands_master.id
WHERE bands_tags.tag = "dark wave"
GROUP BY name
ORDER BY name ASC

Hast du eine Idee woran das liegen könnte?

Vielen Dank & Gruß,
Markus

Sind deine Tabellen indiziert und wenn ja, wie?

Sind deine Tabellen indiziert und wenn ja, wie?

Oh je du stellst mir Fragen..
Kurze Erklärung: Der Quellcode und die Datenbank stammen ursprünglich nicht von mir. Ich lerne nun eben nach und nach, was ich für auftretende Probleme benötige.

Dann schätze ich, sollte ich mich nun mal mit dem Indizieren beschäftigen.

Dann schätze ich, sollte ich mich nun mal mit dem Indizieren beschäftigen.

Schätze ich auch ;)

Schätze ich auch ;)

verdammt... von wegen "ich programmier mal kurz ein neues Feature".. :rolleyes:

Na dann mach ich das mal ;)

Hab folgendes getestet: Die Tabelle bands_master hat in der Spalte "name" (also der Band-Name) ein "Volltext" und ein "Index" erhalten, und die Tabelle bands_tags in der Spalte tag (die Genres) ebenso.
Query für "Dark Wave" braucht damit anstatt 20 Sekunden nur noch 0.3 Sekunden.

Bei anderen Abfragen dauert es aber noch zu lange
gothic metal -> 203 Bands -> 1.9s
thrash metal -> viele bands -> nirwana

War das Käse oder prinzipiell der Richtige Ansatz?

Volltext-Indizes gibt es doch in InnoDB gar nicht. Nutzt du etwa MyISAM? Das würde ich nochmal überdenken, schon aus Gründen der referentiellen Integrität. (Hat mit der Abfrageperformance aber jetzt nicht so viel zu tun.)

Wichtig sind in erster Linie mal Primärschlüssel und Indizes auf deine Pseudo-Fremdschlüssel-Spalten (MyISAM hat ja keine echten Fremdschlüssel). Außerdem macht es unter Umständen Sinn, weitere Spalten zu indizieren, die oft in Where-Klauseln verwendet werden.

Jedenfalls siehst du ja an der Verbesserung der Abfragezeit, dass du im Grunde auf dem richtigen Weg bist. Als Stichwort wäre noch EXPLAIN zu nennen, um das weiter zu optimieren.

Danke!
Dann werkle ich mal weiter und melde mich mit neuen Erkenntnissen :)

Aus dem EXPLAIN werde ich noch nicht wirklich schlau.
Fürs Genre "Black Metal" benötigt die Abfrage bei 497 Bands 93 Sekunden, das EXPLAIN sagt mir folgendes:

Abfrage:
EXPLAIN SELECT bands_master.name, bands_tags.band_id,
GROUP_CONCAT( " ", alle_genres.tag )
FROM bands_tags
JOIN bands_master ON bands_master.id = bands_tags.band_id
JOIN bands_tags AS alle_genres ON alle_genres.band_id = bands_master.id
WHERE bands_tags.tag = "black metal"
GROUP BY name

Das EXPLAIN-Ergebnis:
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE bands_tags ref tag,band_id,tag_2 tag 100 const 488 Using where; Using temporary; Using filesort
1 SIMPLE bands_master eq_ref PRIMARY PRIMARY 3 vampster_01.bands_tags.band_id 1 Using where
1 SIMPLE alle_genres ref band_id band_id 4 vampster_01.bands_master.id 2 Using where

Kann ich nicht wirklich viel damit anfangen.

Das sieht so aus, als ob du noch keine Indizes hättest. Statt "using where" sollte (außer in der ersten Zeile) "using index" stehen.

Hm, eigentlich schon?!
die bands_tags hat jeweils über band_id, tag und naehe einen Index,
die bands_master hat auch Indexe.

alle_genres erzeuge ich durch den Join, da kann ich ja nichts indexieren?

Ah, okay, hatte bei der bands_master wohl doch noch nicht richtig indexiert.

Hab mich von der Meldung "The following indexes appear to be equal and one of them should be removed" abschrecken lassen.
Jetzt hab ich diese Meldung, dafür schnurrt aber die Abfrage?!

Zeig mal bitte die CREATE TABLE Statements.

meinst du das?

CREATE TABLE `bands_master` (
`id` mediumint(9) NOT NULL auto_increment,
`name` varchar(255) NOT NULL default '',
`type` char(3) NOT NULL default 'b',
`status` varchar(10) NOT NULL default '' ,
PRIMARY KEY (`id`),
UNIQUE KEY `name` (`name`),
KEY `name_2` (`name`),
KEY `type_2` (`type`),
KEY `id` (`id`),
FULLTEXT KEY `type` (`type`),
FULLTEXT KEY `name_3` (`name`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=11516 ;

CREATE TABLE `bands_master` (
`id` mediumint(9) NOT NULL auto_increment,
`name` varchar(255) NOT NULL default '',
`type` char(3) NOT NULL default 'b',
`status` varchar(10) NOT NULL default '' ,
PRIMARY KEY (`id`), -- OK
UNIQUE KEY `name` (`name`), -- OK
KEY `name_2` (`name`), -- überflüssig, kann weg
KEY `type_2` (`type`), -- OK
KEY `id` (`id`), -- überflüssig, kann weg
FULLTEXT KEY `type` (`type`), -- überflüssig, kann weg
FULLTEXT KEY `name_3` (`name`) -- überflüssig, kann weg
)
ENGINE=MyISAM -- meine Empfehlung: InnoDB
DEFAULT CHARSET=latin1 -- meine Empfehlung: utf8 collate utf8_general_ci
AUTO_INCREMENT=11516 ;

Aber was ist mit den anderen Tabellen?

CREATE TABLE `bands_master` (
`id` mediumint(9) NOT NULL auto_increment,
`name` varchar(255) NOT NULL default '',
`type` char(3) NOT NULL default 'b',
`status` varchar(10) NOT NULL default '' ,
PRIMARY KEY (`id`), -- OK
UNIQUE KEY `name` (`name`), -- OK
KEY `name_2` (`name`), -- überflüssig, kann weg
KEY `type_2` (`type`), -- OK
KEY `id` (`id`), -- überflüssig, kann weg
FULLTEXT KEY `type` (`type`), -- überflüssig, kann weg
FULLTEXT KEY `name_3` (`name`) -- überflüssig, kann weg
)
ENGINE=MyISAM -- meine Empfehlung: InnoDB
DEFAULT CHARSET=latin1 -- meine Empfehlung: utf8 collate utf8_general_ci
AUTO_INCREMENT=11516 ;

Aber was ist mit den anderen Tabellen?

Ok, vielen dank, überflüssige sind weg.

Kann ich einfach so von MyISAM auf InnoDB umschalten?

Wegen der anderen Tabellen - ich habe schon angefangen, weiter zu indizieren. Es sind über 100 Tabellen, ich habe bei den am häufigsten gebrauchten angefangen, so wie ich denke, dass es sinnvoll ist. Und habe den Eindruck, dass die Seite insgesamt schon schneller geworden ist.

*Vielen* Dank. :)

Kann ich einfach so von MyISAM auf InnoDB umschalten?

Ja: ALTER TABLE tabelle ENGINE = InnoDB;

Ja: ALTER TABLE tabelle ENGINE = InnoDB;

Hab mal etwas zum Thema gelesen. InnoDB benötigt mehr Speicherplatz und ist grundsätzlich langsamer, dafür habe ich den Vorteil, mit Transactions arbeiten zu können und habe referenzielle Integrität? (Was auch immer das bedeutet?)


Und...es wird doch langsam:
genre-guide (http://vampster.com/genres/index2.php4?genre=dark%20wave)

:-)