[Sekundentakt]

Hallo Gemeinde,

mein Server braucht Ewigkeiten für folgende Abfrage:

SELECT `Name`, `Ort`, `eMail` FROM `test` WHERE `UID` IN (SELECT `UID` FROM `test` WHERE `Name` = 'George' OR `Name` = 'Jonathan') HAVING `FID`=115 OR `FID` = 190 OR `FID` = 10084

Insgesamt 17 Sekunden.
Die Tabelle ist etwa 700.000 Datensätze groß.

EXPLAIN verrät mir, dass die äußere Abfrage als type = all hat, keinen Index verwendet und auf 699150 Datensätze via WHERE angewendet wird.

Der Subquery hat type = index_subquery, nutzt den gewünschten Index und wird auf "5 Rows" angewendet. Ebenfalls "using WHERE".

Von einem FORCE INDEX zeigt sich MySQL zusätzlich unbeeindruckt.
Was kann ich da jetzt machen?

Falls relevant:
Die Abfrage soll Name, Ort und eMail von Usern selektieren, die George oder Jonathan heißen UND zusätzlich bestimmte FIDs besitzen.
Alle diese Werte befinden sich in der Selben Tabelle.

Danke für jede Hilfe!

[dyrathror]

Mal ne Frage, wozu brauchst Du für diese Query eine Subquery?
Tut es nicht auch:

SELECT `Name`, `Ort`, `eMail` FROM `test` 
WHERE (`Name` = 'George' OR `Name` = 'Jonathan') 
AND (`FID`=115 OR `FID` = 190 OR `FID` = 10084);

[Sekundentakt]

Hi dyrathor,

das ist richtig.
Dieser Subquery stellt aber auch nur ein (einfaches) Beispiel von vielen dar, bei dem ein Subquery mit IN verknüpft wird.

Vielleicht verdeutlicht ein anderes Beispiel eher die Notwendigkeit.

Konkreter Fall:
Viele Werte passen auf viele Pflanzen
- eine m:n-Beziehung

Kopf-Tabelle
ID  Wert    Eigenschaft1      Eigenschaft2
1    284     etwas               nochwas
2    480     etwas               nochwas
3    540     etwas               nochwas

LOOKUP-Tabelle
ID, BID
1   25
1   26
1   48
2   89
2   48
2   26
3   26
3   48
...

Body-Tabelle
BID, Name, ...
25   Safran, Kastanie, ...

An die Datenbank stelle ich nun eine Suchanfrage.
Die Anfrage "284" liefert mir die BID's 25,26 und 48 in der LOOK-UP-Tabelle.
Folglich werden nun 3 Pflanzen als Ergebnis zurückgeliefert.

Die Anfrage könnte aber auch "284 480" lauten. Folglich werden 4 Pflanzen zurückgeliefert.
In der Realität sind das meistens aber mehr als 40.

Darum möchte ich Filterkriterien einbauen.
Bevor ich den Request "284 480" an die DB absetze, prüfe ich, ob ein Filterwert enthalten ist. Wenn nicht, nutze ich einen 'normalen' SELECT:

SELECT * FROM `body_table` WHERE UID IN
			(SELECT UID FROM `lookup_table` WHERE ID IN 
				(SELECT `ID` FROM `head_table` WHERE `wert`=248 OR `wert`=480))

Wenn aber ein Filterwert enthalten ist, dann möchte ich, dass die Ergebnismenge eingeschränkt wird.

Ungefähr so (Filter: 480) :

SELECT * FROM `body_table` WHERE UID 
			IN
				(SELECT UID FROM `lookup_table` WHERE ID 
			IN 
				(SELECT `ID` FROM `head_table` WHERE `wert`=248)
			HAVING UID 
			IN (SELECT `UID` FROM `lookup_table` WHERE ID 
				IN
					(SELECT `ID` FROM `head_table` WHERE `wert`=480)
				)   
			)

Dadurch erhalte ich nur Pflanzen zurück, auf die definitiv der Wert 480 zutrifft.

Allerdings erhöht sich dann die Ausführungsgeschwindigkeit auf 10-600 (und mehr) Sekunden. Weiterhin wird kein Index mehr verwendet.
Der Fehler erschließt sich mir allerdings auch nicht. Ich bin wirklich ratlos.
Was läuft da falsch?

Eine Idee?

Vielleicht noch zusätzlich:
Ist es normal, dass MySQL Indexe nur für Subqueries nutzt?
Ein EXPLAIN Verrät nämlich, dass für die äußerste Abfrage ein Tabellenscan durchgeführt wird.

[dyrathror]

Hmmm, Du schachtelst die Selects ja ziehmlich freimütig immer in der Hoffnung das der DB-Server das schon alles optimieren kann (na ja, sollte er vielleicht können).

Welche MySQL Server Version und welche DB-Engine nutzt Du denn?

Und wieviele Einträge haben denn die Tabellen die Du da miteinander verbindest?

[Sekundentakt]

Hier mal eine andere Query-Version für den Standard-Select.

SELECT `body_table`.`BID`, `body_table`.`namel` 
		FROM `body_table`, `lookup`
		WHERE `body_table`.`BID`=`lookup`.`BID`
		AND `lookup`.`ID` 
		IN
			(	
				SELECT `head_table`.`ID` 
				FROM `head_table` 
				WHERE `head_table`.`wert`=248 OR `wert`="480"
			) GROUP BY `BID`

Ich nutze MySQL 5.1.33 und das MyISAM - Engine.
Es geht in der HEAD-Tabelle um ca. 3-4mio Einträge, in der LookUp um ca. das 6fache und in der body_tabelle um 2mio Einträge.
Zumindest soll es da hingehen.

Meinen Filter-Subquery müsste ich auch entsprechend umstellen.

Ich denke, dass ich das so lösen werde:

SELECT `UID` FROM `lookup` WHERE  `ID` IN (ein SELECT auf die Filter-Datensätze)

Dann habe ich grob alle `UID`s die definitiv betroffen sind.
Um die Ergebnisse dann noch zu veredeln werden die "Standard"-Werte über die UIDs gejagt - fertig.

Wenn ich mir allerdings mal die Länge des Gesamtquerys vor Augen halte, sieht das nicht mehr nach High-Performance aus...

[$traight-$hoota]

schon mal versucht, die subqueries als eigenständige datenbankabfragen auszuführen und dann die ergebnisse in die jeweiligen hauptqueries einzusetzen?
Vielleicht bringen dann auch die indizes was...

[Sekundentakt]

schon mal versucht, die subqueries als eigenständige datenbankabfragen auszuführen und dann die ergebnisse in die jeweiligen hauptqueries einzusetzen?
Vielleicht bringen dann auch die indizes was...

Eigentlich wollte ich genau DAS nicht - auch wenn es eine Option ist.

Einerseits müssten dann immer wieder neue Queries geöffnet werden, was seine Zeit brauchen wird und andererseits sollte MySQL das von Hause aus selbst erledigen - dafür gibt's ja auch IN & Co.
Sobald eine IN-Abfrage aber auf die Selbe Tabelle wie der Hauptquery angewendet wird, wächst die Ausführungszeit in's unendliche - ich vermute hier einen Fehler meinerseits.

Zusätzlich ist es egal auf welche Tabelle ich einen Subquery starte - die äußere Abfrage führt jedesmal einen Tabellenscan durch und das ist sicherlich nicht im Sinne des Erfinders.

[dyrathror]

Zuerst einmal vielleicht ein kleines Tool das Dir helfen könnte zu analysieren was MySQL gerade macht: http://jeremy.zawodny.com/mysql/mytop/
Ich weiß aber nicht ob Du unter Linux arbeitest aber ich weiß das es für Windows auch Tools gibt die Du zwischen die DB und Deinen Client schalten kannst und die aufzeigen was gerade getan wird.

Als nächstes zu Deiner Query. Das 'IN' in Deiner Query ist natürlich nicht unbedingt performant. Das ist eine Mengenoperation die man nach Möglichkeit vermeiden sollte. Vielleicht kannst Du Dir Query dementsprechend ändern?

Und dann würde ich folgendes versuchen. Stell Deine Query so um das zuerst die Datenmenge aus der lookup Tabelle auf die IDs einschränkst die Du brauchst und erst hinterher machst Du den Vergleich mit der body_table. Das sollte einiges an Performance rausholen.

Insgesamt ist es natürlich nicht unbedingt glücklich wenn Du drei so große Tabellen miteinander verknüpfen mußt. Ich kenne Deinen Anwendungsfall nicht aber vielleicht solltest Du den Source Code noch einmal daraufhin untersuchen ob Du eventuell bereits Daten hast mit denen Du von vorneherein die Ergebnismenge beschränken kannst.

[Sekundentakt]

Laut Manual sollen Joins veraltet sein - den großen Vorteil von Subqueries habe ich aber (noch) nicht gesehen.

Hier mal meine Alternative:

SELECT STRAIGHT_JOIN body.BID, body.Name
			FROM head, lookup FORCE INDEX (PRIMARY), body FORCE INDEX (PRIMARY)
			WHERE (head.wert = "480" OR head.wert = "284")
			AND head.wert = lookup.ID
			AND lookup.BID = body.BID
GROUP BY body.BID

Dieser Query wird fast genau so durchgeführt, wie ich es mir wünsche.
Er nutzt die richtigen Indexe, verknüpft in der richtigen Reihenfolge etc. - lediglich die GROUP BY Klausel führt er beim falschen Query durch.
Das "Using temporary" und "Using filesort" nutzt er nämlich schon in der head-Abfrage, sollte allerdings erst in der Body-Abfrage geschehen, da erst dann die Daten dafür vorhanden sind. Das Abfrage-Ergebnis ist trotzdem richtig.

Das ist allerdings 'nur' der Standardselect, ganz ohne Filterkriterien.
Bei denen werde ich wohl nen Subquery brauchen, oder siehst Du eine Möglichkeit, wie ich das eingrenzen kann?
Anforderung wäre ja, dass dieser Teil der Abfrage

WHERE (head.wert = "284")
			AND head.wert = lookup.ID
			AND lookup.BID = body.BID

Nur noch auf BIDs durchgeführt wird, die head.wert = 480 haben.

Das 'IN' in Deiner Query ist natürlich nicht unbedingt performant. Das ist eine Mengenoperation die man nach Möglichkeit vermeiden sollte.

Soweit ich das verstanden habe, ist IN nur eine Verknüpfung der Rückgabewerte durch OR.
Also, wenn

SELECT * FROM table WHERE ID IN (SELECT Nummer FROM table2 WHERE Name='Lucy')

die Nummern 5,6,9 zurückgibt, dann ist der Query äquivalent zu

SELECT * FROM table WHERE ID = 5 OR ID = 6 OR ID = 9

Oder nicht?

Insgesamt ist es natürlich nicht unbedingt glücklich wenn Du drei so große Tabellen miteinander verknüpfen mußt. Ich kenne Deinen Anwendungsfall nicht aber vielleicht solltest Du den Source Code noch einmal daraufhin untersuchen ob Du eventuell bereits Daten hast mit denen Du von vorneherein die Ergebnismenge beschränken kannst.

Ich hab auch schon dahingehend über Optimierungen nachgedacht.
Die Idee: Ich lege alle BIDs, die auf den Filter-Wert 480 verweisen in eine eigene lookup-Tabelle ab, und führe head-Abfragen über diese spezifische lookup-durch.
Das würde die einzelne Tabellengröße erheblich reduzieren, erhöht aber im Gegenzug die Anzahl an lookup-Tabellen auf die Anzahl an Filterkriterien. Zusätzlich wären die Datenmengen redundant.
Ich hab leider noch nichts zum Thema "Performance von Datenbanken mit vielen Tabellen" gelesen und weiß nicht wie und ob es da Auswirkungen gibt.
Die größte Herausforderung wird dann die Verarbeitung der Anzahl an Filterkriterien in EINER ABFRAGE sein. Wenn jede Abfrage nur ein Filterkriterium enthält, ist die Lösung (wahrscheinlich) ideal, wenn ich aber zwei oder sogar drei Filterkriterien in einer Abfrage nutze, müsste ich mehrere Lookup-Tabellen parallel befragen, die Ihrerseits dann definitiv redundante Datensätze liefern.

Zuerst einmal vielleicht ein kleines Tool das Dir helfen könnte zu analysieren was MySQL gerade macht: http://jeremy.zawodny.com/mysql/mytop/
...

Danke!
Das schaue ich mir morgen mal an.

Zum System:
Ich benutze für die Entwicklung Windows XP. Linux wäre aus Performancesicht besser, da MySQL und PHP einfach mehr Möglichkeiten haben - aber dafür braucht man Ahnung, die ich nicht habe .
Der Server auf dem das Ganze letztendlich laufen soll, wird aber Linux nutzen.

[dyrathror]

Ich habe mal ein wenig ge-googled und folgenden Artikel gefunden:
http://www.xaprb.com/blog/2006/04/30...oins-in-mysql/

Der nutzt zwar in seinem Beispiel die InnoDB Engine aber ich denke das die Aussage auch auf MyISAM zutrifft. Besonders interessant finde ich die "Query-Optimierung" durch MySQL im letzten Beispiel wo MySQL einen Full-Table-Scan in der Hauptquery macht (ohne Nutzung der Indexes) und dadurch für jede einzelne Row die Subquery durchführt. Ich denke so etwas in der Richtung passiert bei Dir auch.

Nebenbei gesagt nutzt MyISAM zum Beispiel Caching Mechanismen des darunterliegenden Betriebsystem, da würde ich unter Windows auch lieber nicht zuviel erwarten, insbesondere bei der Größe Deiner Querys.

[Sekundentakt]

Der von dir gepostete Artikel beschreibt genau mein Eingangsproblem und auch die von mir gepostete Lösung. Insofern nichts Neues, aber zumindest ist jetzt klar, das ich zuvor nicht völligen Bockmist fabriziert habe, sondern MySQL hier allgemein Aufholbedarf hat :).
Übrigens ist die Qualität, in der er das beschreibt, ein Grund mehr das Buch MySQL High Performance zu kaufen!

Nebenbei gesagt nutzt MyISAM zum Beispiel Caching Mechanismen des darunterliegenden Betriebsystem, da würde ich unter Windows auch lieber nicht zuviel erwarten, insbesondere bei der Größe Deiner Querys.

Das wusste ich bisher noch nicht. Danke für den Tipp!
Das ist vor allem für Belastungstests extrem wichtig zu wissen.

Wo wir auch schon bei deiner nächsten Empfehlung wären:

http://jeremy.zawodny.com/mysql/mytop/

Der kann in jedem Fall echt nützlich sein. Ich werd' mal schauen, das ich mir Phyton draufziehe oder eine Alternative finde. Danke nochmals!

Übrigens: Heute werd' ich nicht mehr dazu kommen, den Query weiter zu optimieren.
Aber der nächste Schritt wäre, dass ich einen INNER JOIN mit ON verwende.
Falls jemand hier Erfahrungswerte mit insbesondere GROUP BY hat, wäre es schön, davon zu lesen.

[Sekundentakt]

Die Umformulierung zum INNER JOIN hat kein anderes Abfragemuster erzeugt. Bisher habe ich auch keine Möglichkeit entdeckt, das USING temporary der GROUP BY Klausel auf die Ergebnisse des

lookup.BID = body.BID

Teils zu beschränken.
Stattdessen wird das immer noch in der ersten head-Abfrage gemacht.

Wie gesagt: Wer da Ideen und Anregung hat - immer her damit.
Das wäre, ohne die Tabellenstruktur zu verändern, die im Moment einzige weitere Optimierungsmöglichkeit des Querys.

Hier aber mal die Abfrage mit Filterwerten:

SELECT STRAIGHT_JOIN produkte.Titel
			FROM 
			head AS temp, lookup AS CloseLook FORCE INDEX (ID), 
			head, lookup FORCE INDEX (PRIMARY), body FORCE INDEX (PRIMARY)
			WHERE ((temp.wert = "480")
			AND temp.ID = CloseLook.ID
			AND CloseLook.BID=lookup.BID)
			AND	(head.wert = "248" )
			AND head.ID = lookup.ID
			AND lookup.BID = body.BID
GROUP BY body.BID

Zur Tabellenstruktur:
In der Head-Tabelle liegt ein Index auf der Spalte "wert".
In der Lookup-Tabelle liegt ein Index auf BID und ID (Primary) und einer auf ID (ID)
In der Body-Tabelle liegt ein Index auf BID (Primary).