Performance bei Abfrage inner join

[Tikiwiki]

Hallo zusammen,

ich verwende folgende Abfrage in einer php-Datei die folgendes tut.
Abfrage eines Suchwortes in Dokumenten in Abhängigkeit der Benutzerberechtigung.

SELECT dateien.daid, dateien.titelname, dateien.dateiname, benutzer.benutzername, worte.wort, dateihkuk.fukat
        FROM (
        (
        dateien
        INNER JOIN (
        benutzer
        INNER JOIN dateiberechtigung ON benutzer.userid = dateiberechtigung.fr_userid
        ) ON dateien.daid = dateiberechtigung.fr_daid
        )
        INNER JOIN worte ON dateien.daid = worte.daid)
        INNER JOIN dateihkuk ON dateien.daid=dateihkuk.fdaid
        WHERE (
        (
        (
        worte.wort
        ) = 'suchwort'
        )
        AND (
        (
        dateiberechtigung.fr_userid
        ) = 3114
        )
        )

Auf dem lokalen Rechner bekomm ich in ca. 2 Sekunden das Ergebnis. Wenn ich das gleiche auf dem Server ausführe habe ich 100% CPU-Auslastung und er geht in die Knie.
Auch wenn ich das ganze in phpmyadmin (SQL) ausführe klappt es wunderbar.

Datenbestand:
Tabelle Dateien = 3364 DS
Tabelle Dateiberechtigung = ca. 35000 DS
Tabelle Worte: ca. 130000 DS
Tabelle Benutzer: 13 DS
Tabelle Kategorien: 3364 DS

Frage: Kann ich die Abfrage noch optimieren und wie? Oder könnte es andere Gründe geben?

Danke Euch
Dietmar

[diver-network]

Hi Dietmar,

was mir als Erstes an Deiner Abfrage auffällt sind die vielen Klammern.
Diese sind aber (hoffentlich, musst Du anhand der Resultate sehen) nicht falsch und sollten die Performance nicht beeinflussen.
Dennoch rate ich Dir dazu, überflüssige Klammern zu entfernen, damit es übersichtlicher wird.

Schau mal, ob Du auf Deinem Produktivsystem die selben Indizes verwendest wie auf Deinem Testsystem.
Wichtig sind vor allem Indizes auf die Spalten, über die Verknüpft wird, also:
- dateien.daid
- dateiberechtigung.fr_daid
- benutzer.userid
- dateihkuk.fdaid
- worte.wort (da hier eine Einschränkug mit WHERE erfolgt)

Geb' auch mal den fertigen SELECT Befehl in PHPMyAdmin ein und häng ein "EXPLAIN" davor.
An dem Ergebnis kannst Du erkennen, wie MySQL den SELECT verarbeitet und welche Indizes (nicht) verwendet werden.

HTH,

Andy

[Tikiwiki]

Hi Andy,

danke erstmals für die Antwort.
1. Habe die Klammern entfernt. Diese sind überflüssig.
2. Habe auch die Indizes geprüft und konnte da auch nicht wirklich was feststellen.

Jetzt zu einer weiteren Frage wo ich nur Bahnhof verstehe und mir das MySQL-Handbuch auch nicht wirklich geholfen hat und die Erklärungen in meinem Buch (das nicht schlecht ist) auch nicht helfen.

Habe ein EXPLAIN vorgesetzt und als Ergebnis (Anhang) habe ich dann folgendes bekommen. Vielleicht kann mir das wer erklären was dies nun bedeutet und wie die Abfrage abgearbeitet wird???

Danke
Dietmar

[diver-network]

Hi Dietmar,

leider hast Du das endgültige SQL Statement nicht mit eingebunden, daher nur die Erklärung des EXPLAINs:

Du hast auf den Tabellen "Worte", "Dateiberechtigung" und "dateihkuk" KEINE Indizes (possible keys = NULL), so daß MySQL für die Durchsuchung dieser Tabellen 129853 (alleine in der Tabelle Worte) x 34316 (Tabelle Dateiberechtigung) x 3364 (Tabelle Dateihkuk) Zeilen ansehen muß, um zum gewünschten Ergebnis zu kommen.

"type" im EXPLAIN zeigt die Art des verwendeten ""Suchalgorithmus"" bzw. - Vorgangs an. "ALL" bedeutet hierbei, daß jeder Datensatz angesehen musste, "eq_ref" bedeutet, daß es eine 1 zu 1 Zuordnung zu einer anderen Tabelle gab (z.B. durch einen INNER JOIN), "const" bedeutet, daß der Wert hart im SELECT drin war (z.B. WHERE id = 1).

"possible keys" zeigt die Indizes, die MySQL zur Verfügung hat, um den SELECT Befehl abzuarbeiten. Dies bedeutet aber NICHT automatisch, daß auch alle Indizes verwendet werden müssen.
"key" zeigt den in diesem SELECT verwendeten Index

HTH,

Andy

[EDIT] Bitte füge so kurze Zusatzinformationen wie das Ergebnis des EXPLAIN direkt in Dein Posting ein. Ich schaue mir eigentlich nur ungern Zusatzdokumente zu einem Posting an.

[Tikiwiki]

Hi Andy,

danke für die Erklärungen und bitte um Entschuldigung für den Dateianhang.
Tabellen hier zu formatieren ist ein wenig schwierig und sehen nicht besonders aus.

Hier das SQL-Statement (hoffe Du meinst dieses):

EXPLAIN SELECT dateien.daid, dateien.titelname, dateien.dateiname, benutzer.benutzername, worte.wort, dateihkuk.fukat
FROM dateien
INNER JOIN benutzer
INNER JOIN dateiberechtigung ON benutzer.userid = dateiberechtigung.fr_userid ON dateien.daid = dateiberechtigung.fr_daid
INNER JOIN worte ON dateien.daid = worte.daid
INNER JOIN dateihkuk ON dateien.daid = dateihkuk.fdaid
WHERE worte.wort = 'Suchwort'
AND dateiberechtigung.fr_userid =3114

Also wenn ich das richtig verstanden habe, sollte ich mal alle Felder die Verknüfpungen aufweisen als Index angeben.

Danke Dir auf jeden Fall für die tolle Erklärung und das Du in den Anhang geschaut hast.

Dietmar

[diver-network]

Hi Dietmar,

irgendwas ist mit Deinem SQL Statement falsch. Z.B. fehlt eine Verknüpfung mit ON von dateien nach benutzer.

Hier mal das auf die Schnelle verbesserte SQL Statement von mir. Schau aber unbedingt, ob es das gewünschte Ergebnis bringt!!:

EXPLAIN SELECT dateien.daid, dateien.titelname, dateien.dateiname, benutzer.benutzername, worte.wort, dateihkuk.fukat
FROM benutzer INNER JOIN 
dateiberechtigung ON benutzer.userid = dateiberechtigung.fr_userid
INNER JOIN dateien ON dateiberechtigung.fr_daid = dateien.daid
INNER JOIN worte ON dateien.daid = worte.daid
INNER JOIN dateihkuk ON dateien.daid = dateihkuk.fdaid

WHERE worte.wort = 'Suchwort'
AND dateiberechtigung.fr_userid =3114

Eventuell musst Du aber auch Klammern verwenden, diese sind ja nicht immer falsch, auch wenn zu viele das ganze unübersichtlich(er) machen ;-) ;-)

Bzgl. der Indizes:
Richtig, jedes Feld, über das eine Verknüpfung zu einer anderen Tabelle geht, sollte einen Index haben.
Ebenso ist es sinnvoll, wenn Felder, die im WHERE Teil auftauchen, ebenfalls einen Index haben.
Also:
dateien.daid
worte.daid
dateihkuk.tdaid
benutzer.userid
dateiberechtigung.fr_userid
dateiberechtigung.fr_daid
worte.wort

Bitte beachte aber auch die Nachteile von Indizes, die in sehr seltenen Fällen zum Tragen kommen:
Wenn Du in Relation zur Anzahl der SELECTS sehr sehr viel mehr INSERTS oder UPDATES auf ein Feld hast kann ein Index negative Auswirkungen auf die Performance haben, da die Datenbank diesen bei jeder Wertänderung zusätzlich pflegen muß.
Wie aber gesagt ist dies nur in sehr seltenen Fällen der Fall.

HTH,

Andy

[Tikiwiki]

Hi Andy,

danke nochmals für Deinen Beitrag und die Zusatzerklärungen.

Also nochmals zu einen kurzen Feedback. Seit ich die Indizes gesetzt habe geht alles pipifein. Anscheinend habe ich da bezüglich Sinn von Indizes was verwechselt. Das mit dem Nachteil bei Insert und Update war bekannt (hab drübergelesen und ist auch mal hängen geblieben). Ich dachte wenn das Feld als Integer definiert wurde reicht dies schon. Werde nochmals das Kapitel Indizes nachlesen.

Zu Deinem SQL-Statement. Also ich werde das mal testen, ob ich das gleiche Ergebnis erhalte. Bei meinem Statement (getestet ca. 30 Minuten) konnte ich keine Fehler entdecken, was noch nichts heißen soll. Bei dieser Datenmenge ist nicht so leicht den Überblick zu behalten.

So ich danke Dir nochmals das Du Deine Zeit meinem Problem gewidmet hast.
Jetzt liegt es an mir und ich betrachte dieses Thema als positiv abgehandelt.

LG
Dietmar

[diver-network]

Hi Dietmar,

da bin ich ja froh, daß es was geholfen hat.
Nur kurz wg. Deiner falschen Vermutung, daß die Felddefinition als INTeger was bringt und für alle, die etwas ähnliches denken:

Die Typdefinition eines Feldes, also INTeger, CHAR, VARCHAR etc. hat u.a. folgende Aufgaben:
1) Sie gibt vor, was für Zeichen in der entsprechenden Spalte erlaubt sind. In INTeger Spalten sind nur Zahlen erlaubt, in CHARacter Spalten kann quasi alles abgespeichert werden, allerdings sollten reine Zahlenwerte nach Möglichkeit auch in INTeger Spalten abgelegt werden.

Gleichzeitig wird bei INTeger Datentypen noch ein Wertebereich vorgegeben; Z.B. dürfen in als TINYINT definierten Spalten nur relativ kleine Zahlen (hab den genauen Wert leider nicht im Kopf, glaube aber, daß diese von -256 bis + 256 gehen) abgespeichert werden, in BIGINT definierten Feldern geht der Wertebereich von - [einige Millionen] bis + [einige Millionen].
2) Sie erlaubt eine "sinnvolle" Sortierung des Spalteninhalts über ORDER BY.
3) Sie definiert den benötigten Speicherplatz pro Datensatz in der Tabelle. So kann man schon im Voraus abschätzen, wie groß in etwa die Festplatte sein muß, auf der die Datenbank abgelegt ist. Dies natürlich nur, wenn man eine Vorahnung davon hat, wie viele Datensätze kommen werden.

Der Spaltentyp hat in der Regel KEINE Auswirkung auf die Performance der Datenbank.

Wichtig ist, daß bei verknüpften Tabellen die "Verknüpfungsspalten", also die Spalten, die im SELECT per JOIN verbunden werden, den selben Datentyp haben. Ansonsten geht die Performance rapide bergab oder es ist im schlimmsten Fall gar kein JOIN möglich.

Gruß,

Andy