nicht angemeldet
& (MySQL) Komplizierter Query
Hallo,
für eine Datenbank abfrage aus einem Multiple Select formular (http://server.cwd.at/jobforum1/suchen.php3) bau ich mir mit php den MySQL query zusammen.
Ich mache folgendes für jedes feld:
/* BERUFSGRUPPE */
$leer = "0";
for ($i=0;$i<count($berufsgruppe);$i++):
if ($berufsgruppe[$i] == "alle") {
/* echo "alles gewählt"; */
}else{
if ($leer == "0") {
$query .= " AND berufsgruppezw.berufsgruppeid = '$berufsgruppe[$i]'";
$leer = "1";
}else{
$query .= " OR berufsgruppezw.berufsgruppeid = '$berufsgruppe[$i]'";
}
}
endfor;
$leer="0";
/* END BERUFSGRUPPE */
Bei einer Suche wo von jedem feld 2 stück gewählt wurde kommt daher so ein query zusammen:
SELECT DISTINCT ausschreibung.ausschreibungsid FROM ausschreibung,berufsgruppezw,spezialbereichzw,regionzw,artzw WHERE ausschreibung.ausart = 'gesuch' AND berufsgruppezw.berufsgruppeid = '11' OR berufsgruppezw.berufsgruppeid = '12' AND spezialbereichzw.spezialbereichid = '16' OR spezialbereichzw.spezialbereichid = '5' AND regionzw.regionid = '11' OR regionzw.regionid = '6' AND artzw.artid = '6' OR artzw.artid = '7';
Durch den Join über 5 Tabellen ist das ganze aber so dermassen langsam das so ein query direkt an den mysql client abgegeben ca. 5 minuten braucht, gibt es da auch einen anderen weg?
Ich habe die befürchtung daß das ganze problem daher kommt das es multiple select felder sind, wenn ich von jeder option nur ein feld hätte wäre es um einiges leichter :(
Danke für eure hilfe
lg
Ludwig
-
Hallo Ludwig,
ohne nähere Kenntnis von MySEL kann ich leider nur nur ganz allgemein antworten.
Der Großteil Deiner Query verändert sich nicht. Möglicherweise kommst du mit einer Hilstabelle weiter. Probiere einmal die Query in zwei aufeinander folgende Abfragen aufzuspalten.
SELECT DISTINCT ausschreibung.ausschreibungsid, berufsgruppezw.berufsgruppeid FROM ausschreibung,berufsgruppezw,spezialbereichzw,regionzw,artzw WHERE ausschreibung.ausart = 'gesuch' AND berufsgruppezw.berufsgruppeid = '11' OR berufsgruppezw.berufsgruppeid = '12' AND spezialbereichzw.spezialbereichid = '16' OR spezialbereichzw.spezialbereichid = '5' AND regionzw.regionid = '11' OR regionzw.regionid = '6' AND artzw.artid = '6' OR artzw.artid = '7';
Das wäre der erste Teil der Query. Schreibe doch dieses Ergebnis in eine temporäre Tabelle und lasse Deine Schleife über diese Tabelle laufen. Vielleicht spart dies Zeit ein.
Viele Gruesse
Kess-
Schreibe doch dieses Ergebnis in eine temporäre Tabelle und lasse Deine Schleife über diese Tabelle laufen. Vielleicht spart dies Zeit ein.
Kann mySQL views? Das wäre dann die "temporäre Tabelle" ...
-
Hallo Michael,
Schreibe doch dieses Ergebnis in eine temporäre Tabelle und lasse Deine Schleife über diese Tabelle laufen. Vielleicht spart dies Zeit ein.
Kann mySQL views? Das wäre dann die "temporäre Tabelle" ...
Leider eben nicht. Vom Ergebnis her stimmt das schon, von der Performance her jedoch nicht. Im Gegenteil, sie wird sogar noch schlechter. Eine View wird üblicherweise in den Systemtabellen der Datenbank als SQL-Code eingetragen und erst zum Zeitpunkt des Aufrufes auch ausgeführt. Gegenüber einer temporären Tabelle würde die Teilabfrage (= View) also nicht einmal ausgeführt sondern bei jedem Zugriff erneut. (Schlaue Datenbanken mit ausgefeilten Cache-Methoden einmal ausgenommen. Dazu dürfte MySQL aber wohl nicht gehören.) Performancegewinn ist damit nicht verbunden. Eher ein kaum meßbarer Verlust durch den zusätzlichen Verwaltungsoverhead. Der SQL-Code der View muß schlließlich erst einmal aus den Systemtabellen gelesen werden. Und das geschieht bei jedem Zugriff auf die View.
Viele Gruesse
Kess
-
-
-
WHERE ausschreibung.ausart = 'gesuch' AND berufsgruppezw.berufsgruppeid = '11' OR berufsgruppezw.berufsgruppeid = '12' AND spezialbereichzw.spezialbereichid = '16' OR spezialbereichzw.spezialbereichid = '5' AND regionzw.regionid = '11' OR regionzw.regionid = '6' AND artzw.artid = '6' OR artzw.artid = '7';
Puh. Kannst Du diesen Ausdruck mal klammern? Mit Prioritäten von Operatoren habe ich es nicht so ...
Wenn Du "artid = 6 OR 7" haben willst, dann müßte eigentlich auch "artid in (6, 7) gehen (und wahrscheinlich schneller sein).
Durch den Join über 5 Tabellen ist das ganze aber so dermassen langsam das so ein query direkt an den mysql client abgegeben ca. 5 minuten braucht, gibt es da auch einen anderen weg?
Kess' Idee mit Zwischentabellen ist für Performance sicher gut (für Parallelität natürlich eine Katastrophe, wenn zwei Benutzer dieselbe Tabelle anlegen wollen - hoffentlich brauchst Du so etwas nicht). Echt temporäre (prozeßlokale) Tabellen kenne ich in SQL nicht.
Es kann übrigens gut sein, daß Du durch die *Reihenfolge* der Joins bzw. der Ausdrücke in der Where-Klausel die Geschwindigkeit massiv beeinflussen kannst. (Ein wirklich guter Optimizer sollte das eigentlich selbst richtig machen, aber wo gibt es den schon?) Der Trick ist, daß Du die stark projezierenden JOINs zuerst machst, um die Treffermenge insgesamt einzuschränken - und was bei Dir wie stark projeziert, darüber hat auch ein guter Optimizer (Kommando ANALYZE TABLE in Oracle) bestenfalls statistische Informationen.
Ich habe mal in Oracle zwei Tabellen, die ich fast nur mit einem gemeinsamen JOIN über Primärschlüsselattribute anspreche, in einen CLUSTER gelegt, d. h. die Datensätze beider Tabellen werden so physikalisch gespeichert, daß sie in einem gemeinsamen Segment über den JOIN-Schlüssel adressiert werden können (das minimiert die Kopfbewegungen der Platte usw.). Oracle kann so etwas halt ... aber mySQL ... ?
-
Es kann übrigens gut sein, daß Du durch die *Reihenfolge* der Joins bzw. der Ausdrücke in der Where-Klausel die Geschwindigkeit massiv beeinflussen kannst. (Ein wirklich guter Optimizer sollte das eigentlich selbst richtig machen, aber wo gibt es den schon?) Der Trick ist, daß Du die stark projezierenden JOINs zuerst machst, um die Treffermenge insgesamt einzuschränken - und was bei Dir wie stark projeziert, darüber hat auch ein guter Optimizer (Kommando ANALYZE TABLE in Oracle) bestenfalls statistische Informationen.
Ich habe mal in Oracle zwei Tabellen, die ich fast nur mit einem gemeinsamen JOIN über Primärschlüsselattribute anspreche, in einen CLUSTER gelegt, d. h. die Datensätze beider Tabellen werden so physikalisch gespeichert, daß sie in einem gemeinsamen Segment über den JOIN-Schlüssel adressiert werden können (das minimiert die Kopfbewegungen der Platte usw.). Oracle kann so etwas halt ... aber mySQL ... ?
Jepp. Bei Oracle kann man damit unheimlich viel herausholen. Auch der alte Trick bei Oracle, die Reihenfolge der Tabellen in der From Clause funktioniert immer noch (die kleinste Tabelle muß zuletzt angegeben sein. Oder war das jetzt zuerst ? *schwitz, vergeßlich*) Jede gute Datenbank kennt Regeln zur Optimierung. Dazu ist dann allerdings schon wieder Verständnis für die Speicherungs - und Zugriffsmethoden der Datenbank nötig.
Oft hilft es auch Tabellen in ein Segment zu legen, oder bewußt zu trennen, Locking Mechanismen (also Sperren gegen gleichzeitige Zugriffe) bedeuten Verwaltungsoverhead. Tablespacelocking kann zwar in Multiuserumgebungen tödlich sein, es kann aber auch gegenüber Page- oder gar Row-Locking einen ordentlichen Geschwindigkeitsschub bringen. Regelmäßige Reorganisation, angepaßte Freespaceangaben (freier Platz aud jeder Tabellenseite für änderungen und neue Sätze) usw. tun ein übriges. Man kann eine Wissenschaft daraus machen. Das geht hier aber denke ich zu weit. Zumal dies dann auch andere Anwendungen betrifft, die ebenfalls auf die Tabellen zugreifen. Wer so tief in die Materie einsteigt, sollte sich dann besser gleich eine 'gescheite' (nicht hauen bitte) Datenbank zulegen. ;-)Viele Gruesse
Kess-
Wer so tief in die Materie einsteigt, sollte sich dann besser gleich eine 'gescheite' (nicht hauen bitte) Datenbank zulegen. ;-)
Hallo,
Stimmt leider alles, MySQL kann die netten sachen einfach nicht, das letzte mal als ich sowas komplizierteres gemacht habe hatte ich eine Adabas DB im Hintergrund, bei der sind Views kein problem, da geht das und sogar sehr sehr schnell ohne komplizierte select bzw join konstrukte.
lg
Ludwig -
Jede gute Datenbank kennt Regeln zur Optimierung. Dazu ist dann allerdings schon wieder Verständnis für die Speicherungs - und Zugriffsmethoden der Datenbank nötig.
Hm ... *eigentlich* sollte das ja der Optimizer machen.
Man kann eine Wissenschaft daraus machen. Das geht hier aber denke ich zu weit. Zumal dies dann auch andere Anwendungen betrifft, die ebenfalls auf die Tabellen zugreifen.
Genau. Schon in Oracle 7 hatte das Kapitel "The Optimizer" im "Server Concepts"-Handbuch 65 Seiten ...
Die Konzepte für performance tuning, die mir inzwischen noch eingefallen sind, sind:
a) Ausführungpläne ansehen - Oracle kann in einem trace modus jede Transaktion protokollieren und zeigen, welche Tabellenzugriffe als full table scan und welche über welche Indexe erfolgen. Da sieht man dann, was er wirklich tut.
b) "hints" - bei Oracle kann man in einer speziellen Syntax in die SELECT-Anweisungen hineinschreiben, wie sie ausgeführt werden soll (also 3GL-Denken). Dabei kann man festlegen, wo full table scans und wo Indexzugriffe erfolgen sollen und anderes mehr.
c) Vektorielle Ergebnispuffer - ein "fetch" holt nicht nur einen Treffer, sondern gleich ein paar tausend (wenn man genügend RAM übrig hat).
Das hat mir mal ein Programm, welches eine Tabelle mit 15 Mio. Datensätzen nahezu linear verarbeitet, um Faktor 6 beschleunigt, bloß weil jetzt nicht mehr 15 Mio "fetch" erfolgten, sondern nur noch 15000 ...Daß die Reihenfolge der from-Angaben die Reihenfolge der Joins beeinflussen kann, hat Kess schon erwähnt - dies in Verbindung mit den Ausführungsplänen erlaubt es, tatsächlich einen komplexen Join handzuoptimieren, auch schon ohne (proprietäre) "hints".
Wenn ich jetzt noch wüßte, ob *irgendwas* davon in mySQL existiert ...
Und weil man das Naheliegende ja gerne übersieht: Was für Indexe hast Du denn auf Deinen Tabellen?
Wer so tief in die Materie einsteigt, sollte sich dann besser gleich eine 'gescheite' (nicht hauen bitte) Datenbank zulegen. ;-)
Meine Rede seit '33 ... :-)
-
Wer so tief in die Materie einsteigt, sollte sich dann besser gleich eine 'gescheite' (nicht hauen bitte) Datenbank zulegen. ;-)
Hallo,
Stimmt leider alles, MySQL kann die netten sachen einfach nicht, das letzte mal als ich sowas komplizierteres gemacht habe hatte ich eine Adabas DB im Hintergrund, bei der sind Views kein problem, da geht das und sogar sehr sehr schnell ohne komplizierte select bzw join konstrukte.
lg
Ludwig
-
-
-
Hallo Ludwig!
Hab ich jetzt ein Brett vorm Kopp?
1. Wo ist Dein JOIN?
2. Wieso legst Du Bedingungen auf Tabellen, die nicht mit im SELECT-Statement drin sind?SELECT DISTINCT ausschreibung.ausschreibungsid FROM ausschreibung,berufsgruppezw,spezialbereichzw,regionzw,artzw WHERE ausschreibung.ausart = 'gesuch' AND berufsgruppezw.berufsgruppeid = '11' OR berufsgruppezw.berufsgruppeid = '12'
<Schlamassel>
AND spezialbereichzw.spezialbereichid = '16' OR spezialbereichzw.spezialbereichid = '5' AND regionzw.regionid = '11' OR regionzw.regionid = '6' AND artzw.artid = '6' OR artzw.artid = '7';
</Schlamassel>Durch den Join über 5 Tabellen ist das ganze aber so dermassen langsam das so ein query direkt an den mysql client abgegeben ca. 5 minuten braucht, gibt es da auch einen anderen weg?
Eben nicht! Deine Ergebnisse der Query duerften recht seltsam sein. IMHO muesste die Query etwa so aussehen:
<schnipp>
SELECT DISTINCT ausschreibung.ausschreibungsid
FROM (((artzw INNER JOIN ausschreibung ON artzw.artid = ausschreibung.ausschreibungsid) INNER JOIN berufsgruppezw ON ausschreibung.ausschreibungsid = berufsgruppezw.berufsgruppeid) INNER JOIN regionzw ON berufsgruppezw.berufsgruppeid = regionzw.regionid) INNER JOIN spezialbereichzw ON regionzw.regionid = spezialbereichzw.spezialbereichid
WHERE (((ausschreibung.ausart)='gesuch') AND ((berufsgruppezw.berufsgruppeid)=11) AND ((regionzw.regionid)=11) AND ((spezialbereichzw.spezialbereichid)=16) AND ((artzw.artid)=6)) OR (((berufsgruppezw.berufsgruppeid)=12) AND ((regionzw.regionid)=6) AND ((spezialbereichzw.spezialbereichid)=5) AND ((artzw.artid)=7));
</schnapp>Zur Erklaerung:
Ich habe hier die Tabellen jeweils ueber die id verknuepft (was eigentlich bloedsinn ist, das jeweils die Verknuepfungs-ID sein muss, aber aus Zeitmangel halt). Die JOIN-Anweisungen sind hier INNER JOIN, muessen aber LEFT JOIN sein, wenn in der linken Tabelle mehr datensaetze ausgewaehlt werden, wie in der rechten bzw. RIGHT JOIN, wenn andersrum. Es ist halt nur auf die Schnelle zusammengebastelt.
Kleiner Tipp: Wenn Du ein Datenbankprogramm hast (z.B. Access), bastel Dir die DB einfach nach und ertselle die Querys WYSIWYG.
Hoffe, Du kommst damit zurecht (ganz schoen leidiges Thema, meine laengste Abfrage hab ich per VBA erstellt, ein UNION aus mehreren Tabellen mit mehreren JOINS, etwa 1000 Zeichen lang)
Gruß
Thomas-
Hallo Thomas,
kann es sein, daß du Access oder ähnliches M$ SQL gewohnt bist ? :-)
Viele Gruesse
Kess -
Hab ich jetzt ein Brett vorm Kopp?
- Wo ist Dein JOIN?
- Wieso legst Du Bedingungen auf Tabellen, die nicht mit im SELECT-Statement drin sind?
Ein "Join" ist ein semantisches Konzept, kein syntaktisches. Das, was Du hingeschrieben hast, ist kein SQL, sondern proprietäres Gruselwusel.
Die Idee von SQL ist eigentlich, eine 4GL zu sein, also eine Sprache, in der man nicht beschreibt, wie man zu einer Lösung kommt (darum kümmert sich die Datenbank-Engine selbst), sondern nur, wie diese Ergebnisse aussehen sollen.
Daß Access und Konsorten (?) von diesem Konzept abgewichen sind und dem Anwender verschiedene Mechanismen des "Join" durch zusätzliche Syntax zur Verfügung stellt, macht die Sache erstens hochgradig inkompatibel zur "realen SQL-Welt" und zweitens darf man bei einer Änderung des Tabellenkonzeptes seine gesamten 3GL-Optimierungen den Hasen geben, während eine *gute* Datenbank (und da fällt mir halt nur Oracle ein) sich um so etwas selber kümmert.
-