SELFPHP: Version 5.8.2 Befehlsreferenz - Tutorial – Kochbuch – Forum für PHP Einsteiger und professionelle Entwickler

SELFPHP


Professional CronJob-Service

Suche



CronJob-Service    
bei SELFPHP mit ...



 + minütlichen Aufrufen
 + eigenem Crontab Eintrag
 + unbegrenzten CronJobs
 + Statistiken
 + Beispielaufrufen
 + Control-Bereich

Führen Sie mit den CronJobs von SELFPHP zeitgesteuert Programme auf Ihrem Server aus. Weitere Infos



:: Buchempfehlung ::

Einführung in XHTML, CSS und Webdesign

Einführung in XHTML, CSS und Webdesign zur Buchempfehlung
 

:: Anbieterverzeichnis ::

Globale Branchen

Informieren Sie sich über ausgewählte Unternehmen im Anbieterverzeichnis von SELFPHP  

 

:: Newsletter ::

Abonnieren Sie hier den kostenlosen SELFPHP Newsletter!

Vorname: 
Name:
E-Mail:
 
 

Zurück   PHP Forum > SELFPHP > MySQL/MySQLi

MySQL/MySQLi Anfänger, Fortgeschrittene oder Experten können hier Fragen und Probleme rund um MySQL/MySQLi diskutieren

Antwort
 
Themen-Optionen Ansicht
  #1  
Alt 17.02.2006, 13:10:45
Tikiwiki Tikiwiki ist offline
Anfänger
 
Registriert seit: Apr 2005
Beiträge: 115
Performance bei Abfrage inner join

Hallo zusammen,

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

Code:
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
Mit Zitat antworten
  #2  
Alt 17.02.2006, 14:14:36
diver-network diver-network ist offline
Junior Member
 
Registriert seit: Apr 2003
Ort: TÜ
Beiträge: 337
AW: Performance bei Abfrage inner join

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
Mit Zitat antworten
  #3  
Alt 20.02.2006, 13:14:01
Tikiwiki Tikiwiki ist offline
Anfänger
 
Registriert seit: Apr 2005
Beiträge: 115
AW: Performance bei Abfrage inner join

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
Angehängte Dateien
Dateityp: doc EXPLAIN.doc (32,0 KB, 228x aufgerufen)
Mit Zitat antworten
  #4  
Alt 20.02.2006, 14:27:34
diver-network diver-network ist offline
Junior Member
 
Registriert seit: Apr 2003
Ort: TÜ
Beiträge: 337
AW: Performance bei Abfrage inner join

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.

Geändert von diver-network (20.02.2006 um 14:30:57 Uhr)
Mit Zitat antworten
  #5  
Alt 20.02.2006, 14:53:01
Tikiwiki Tikiwiki ist offline
Anfänger
 
Registriert seit: Apr 2005
Beiträge: 115
AW: Performance bei Abfrage inner join

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):
Code:
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
Mit Zitat antworten
  #6  
Alt 20.02.2006, 15:20:54
diver-network diver-network ist offline
Junior Member
 
Registriert seit: Apr 2003
Ort: TÜ
Beiträge: 337
AW: Performance bei Abfrage inner join

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!!:

Code:
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
Mit Zitat antworten
  #7  
Alt 20.02.2006, 16:13:59
Tikiwiki Tikiwiki ist offline
Anfänger
 
Registriert seit: Apr 2005
Beiträge: 115
AW: Performance bei Abfrage inner join

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
Mit Zitat antworten
  #8  
Alt 21.02.2006, 09:46:50
diver-network diver-network ist offline
Junior Member
 
Registriert seit: Apr 2003
Ort: TÜ
Beiträge: 337
AW: Performance bei Abfrage inner join

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
Mit Zitat antworten
Antwort


Aktive Benutzer in diesem Thema: 1 (Registrierte Benutzer: 0, Gäste: 1)
 
Themen-Optionen
Ansicht

Forumregeln
Es ist Ihnen nicht erlaubt, neue Themen zu verfassen.
Es ist Ihnen nicht erlaubt, auf Beiträge zu antworten.
Es ist Ihnen nicht erlaubt, Anhänge hochzuladen.
Es ist Ihnen nicht erlaubt, Ihre Beiträge zu bearbeiten.

BB-Code ist an.
Smileys sind aus.
[IMG] Code ist aus.
HTML-Code ist aus.

Gehe zu

Ähnliche Themen
Thema Autor Forum Antworten Letzter Beitrag
sql Abfrage brauche Hilfe Join oder ??? illetuffi MySQL/MySQLi 1 08.12.2005 11:52:00
Abfrage LEFT JOIN verdreht die Plausibilitäten Franzx MySQL/MySQLi 5 06.12.2005 13:19:59
INNER JOIN Zudecke MySQL/MySQLi 14 23.08.2005 13:28:40
Optimierung bei INNER JOIN mit großen Tabellen henning666 MySQL/MySQLi 8 27.07.2005 14:42:25
SQL Abfrage (INNER JOIN) AlexWeber MySQL/MySQLi 6 08.10.2003 10:10:39


Alle Zeitangaben in WEZ +2. Es ist jetzt 04:24:35 Uhr.


Powered by vBulletin® Version 3.8.3 (Deutsch)
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.


© 2001-2020 E-Mail SELFPHP OHG, info@selfphp.deImpressumKontakt