Hallo,

ich habe hier diesen code schnipsel aus meiner Seite:

Diese Abfrage dauert extrem lange, da sehr viele datensätze in der db enthalten sind.
(Tabelle Keyword z.b. ca. 1.Mio)
Und anschliesend wird das keyword noch überprüft ob es in einem Text vorkommt. (Der text hat ca. 5000Zeichen)

Der Text ist in der Variablen "$nam" enthalten.

Welche möglichkeiten seht ihr diese Abfrage zu beschleunigen? Welchen code hier könnte ich optimieren?


MFG

Hallo Borsti,

Das setzen von Indexen kann MYSQL-Abfragen in grossen Tabellen unglaublich beschleunigen. Setze einfach auf jedem Feld nach dem sortiert oder eingeschränkt wird einen Index! Bei so grossen Tabellen ist es unumgänglich gezielt Index'es zu setzen, du wirst sicher einen Performance-Unterschied bemerken.

Ich empfehle dir mal Indexes auf folgenden Feldern:

anzeige.kosten
anzeige.id (Da Primary -> hat wahrscheinlich schon einen Index)
anzeige.userid
keywords.userid
keywords.anzeigeid
keywords.keyword
users.UserID

Aber am besten liest du dich in das Thema ein, damit du selbst merkst wo wichtige Indexes zu setzen sind. Die Indexes kannst du im PhpMyAdmin setzen.

Gruess,
Joel

Hallo,

danke für deine schnelle antwort! Aber kannst du mir noch erklären was ein "index" in mysql in etwa macht? Und sollte ich den Index vieleicht auf die id setzen die jeder datensatz bei mir hat. Dies ist auch der Primary Key! Oder macht das keinen sinn?

MFG

Primary Keys haben schon einen Index normalerweise -> macht keinen sinn.

Angenommen du hast eine Tabelle mit 100 Namen. Diese Tabelle ist nach dem Feld id sortiert. Wenn du jetzt eine Abfrage machst

WHERE name="hans";

und "hans" hat die id 70, wurden 70 Datensätze durchgekämmt bis zum Namen zu kommen.

Wenn du aber einen Index auf Namen setzt, wird eine neue kleine Tabelle im Hintergrund erzeugt, die die Tabelle mit den Namen auch nach den Namen sortiert hat.

Dann greift es mal auf 50 zu und merkt dass er noch nicht bei "H" ist, dann greift er auf 75 zu und merkt dass er schon über "H" ist, etc.

Weisst du was ich meine?

Bei 100 Datensätzen braucht MYSQL ohne Index ca. 50 Abfragen und mit Index maximal 7 Abfragen!

Greets,
Joel

Was ich noch vergessen habe, SPARSAM mit den Indexen umgehen. Wenn du überall einen Index setzt, braucht deine Datenbank sonst schnell fast doppelt so viel. INSERT's, UPDATE's, DELETE's werden ein bisschen langsamer, dafür SELECT's schneller .

Danke, für die Erklärung.

Ich habe jetzt einmal die index so gesetzt wie du es geschrieben hast. jetzt dauert die abfrage aber fast doppelt so lange.

Also scheint das vieleicht nicht der richtige weg zu sein? Oder liegt es an den gewählten index?

MFG

PS:

Die selects sind mir am wichtigsten!

Hallo,

könntest Du dazu etwas mehr Informationen nennen, so ist es nicht gerade leicht eine Datenbankabfrage zu optimieren. Wie sind die Tabellen aufgebaut (bitte den kompletten Aufbau mit Index usw.)? Von welchen Typ sind die Tabellen (InnoDB, MyISAM, Memory)? Welche MySQL Version wird verwendet? Wie viel Platz verbraucht jede der Abgefragten Tabellen (Speichergröße)?
Laufen vielleicht noch andere Prozesse im Hintergrund, außer der Datenbank Server? Werden vielleicht nebenher noch andere Abfragen durchgeführt?


Grundsätzlich noch, jeder Test sollte mind. 2 durchgeführt werden (falls halt ein andere Prozess zu dem Zeitpunkt läuft und dadruch die Preformance nach unten zieht.


Der Primary Key wird auch indiziert.



MfG Sascha Ahlers

Hallo,

hier noch die gewünschten daten:

MySQL Version: 4.0.15
Tabellen Typ: MyISAM
Tabellen Größe
users: 2KB (da noch keine User vorhanden)
anzeige: 3KB (da noch keine User vorhanden)
keywords: ca. 50 MB
Tabellen Struktur
Keywords

anzeige

users

Dies ist möglich da ich keinen eigenen Server besitze sondern, nur ein Webhosting angebot.
Das ich natürlich die Geschwindigkeit mit einem eigenen server beschleunigen kann ist mir klar. Aber eine Frage hätte ich gleich noch dazu, und zwar was der server haben sollte damit mysql abfragen schnellst möglich bearbeitet werden. (z.b. RAM, CPU, ...)

Ja das habe ich gemacht sogar 5 mal.


MFG

Erster Vorschlag, optimier mal Deine Tabellen, muss denn fast jedes Feld vom Type 'text' sein? Muss 'longtext' wirklich für das Feld 'anzeige' sein, reichen denn keine 65.535 Zeichen (text) oder 16.777.215 Zeichen (mediumtext), müssen es 4.294.967.295 Zeichen (longtext) sein?
Kleine Frage noch, speicherst Du in dem Feld 'keyword' bei der Tabelle 'keywords' ein Wort oder mehrere? - Nach Deiner Abfrage zu urteilen, vermute ich mal nicht.
Warum ist dann das Feld 'keyword' vom Typ 'text'? - 255 Zeichen sollten doch auch reichen.

Ich vermute mal, es lässt sich wesendlich mehr Preformance bei den Abfragen erzielen, wenn Du Deine Tabellen erstmal vernünftig optimiert hast. Nach der Optimierung kann man sich dann erst Gedanken machen, wie man die Datenbank-Abfrage am sinnvollsten beschleunigen kann, auch wie und wo man entsprechende Indexe einbaut.

Zum Hardware-Kauf kann ich nur sagen, dass man kann durch die Optimierung einer Abfrage meistens mehr als das 1000 fache an Geschwindigkeit herausholen. Durch den Umzug auf einen schnelleren Server aber oft nicht mehr als das 10 fache an Geschwindigkeit. Bei der Hardware kommt es auch ganz auf die Aufgaben des Datenbank-Servers an... (meistens sollte aber die I/O-Last sehr hoch sein, Festplatte usw.)

Apropro, sehr empfehlenswertes Buch, vorausgesetzt man kenn schon die Grundlagen von Datenbank und ist einigermaßen mit der MySQL-Abfragesprache vertraut: High Performance MySQL - Optimierung, Datensicherung, Replikation & Lastverteilung" von O'Reilly


<edit>
Ein Index auf das Feld 'anzeigenid' in der Tabelle 'keywords' wäre aber auf jedenfall noch ratsam, da dies der Fremdschlüssel für die Beziehung darstellt.
</edit>



MfG Sascha Ahlers

Bearbeitet von Sascha Ahlers am So 15.01.2006, 01:55

Danke, also du scheinst dein handwerk zu verstehen. Also ich habe jetzt nur durch das ändern der Feld typen fast nen viertel kürzere Ladezeit. Hier einmal meine änderungen:

keywords

anzeige

users

Also jetzt habe ich garkein Text feld mehr.

Ja ich speichere dort auch z.b. "Vielen Dank" ab. Also mehrere Wörter jedoch habe ich das Feld jetzt in den typ varchar geändert und auf 50 Zeichen begrenzt da dieses eigentlich nicht überschritten wird.

Das beste wird voll beides sein. Eine optimierte tabellen Strucktur plus abfragen und die dazu gehörige Leistung der Server landschaft.

Ich kaufe mir zwar ehr selten solche literatur, aber dies scheint mir eine lohnende Investition! Danke für die Empfehlung

Also immer wenn ich andere Indexe setze dauert die Abfrage länger. Eventuell ändert sich dieser Zustand erst wenn auch in der "anzeigen" Tabelle einige Daten vorhanden sind. Dies werde ich dann mal im Testbetrieb näher verfolgen.

Wie oben geschrieben ist durch die Tabellen optimierung auf jeden fall einiges an Performance raus zu holen.

Wenn du noch weitere Ideen hast , z.b. nun auf die Abfrage betreffend würde ichz mich sehr freuen.

<edit>
Ich habe mir auch noch einige gedanken über die optimierung gemacht und mir sind folgende Ideen gekommen, wo ich aber nicht weiß wie man diese umsetzen kann.

1. Ich gruppiere ja die Datensätze, da es sehr viele datensätze gibt die doppelt vorkommen. Kann man es nicht irgendwie so machen das er erst gruppiert, damit es weniger datensätze sind?

2. Ich überprüfe ja mit:
anzeige.kosten <= users.ageld
ob der user genügend geld auf seinem konto hat. Das macht er ja denke ich mit jedem datensatz. Aber da so ca. 3000 Datensätze (keywords) zu einem user gehören, könnte man es nicht nur einmal überprüfen lassen?

3. Ist es sinvoll mit join zu arbeiten anstatt die abfrage z.b. einzeln dann in der While schleife zu behandeln?

Ich hoffe ich habe die ideen sind nicht all zu weit hergeholt, da es ja doch schon sehr spät ist und ich sehr müde, doch ich musste jetzt meine gedanken ströme loswerden ;-)

</edit>


Vielen, Vielen, Vielen, Vielen, Dank an dieser Stelle.


PS: Ich verfalle gerade in einen richtigen optimierungs wahn meiner gesamten Tabellen Struckturen, auch von anderen projekten.

Ob JOIN was bringt, hängt immer davon ab, ich arbeite normalerweiße überhaupt nicht mit dem Befehl Join, sondern arbeite komplett über Where-Klauseln, da dies für mich einfach zu verstehen ist.
Außerdem hängt die Optmierung des auch von dem den internen MySQL-Query-Optimierer ab, i. d. R. erziehlt dieser oft bessere Ergebnisse bei Abfragen, dies ist aber nicht unbedingt immer so.

Zu der Query-Optimierung, leider ist dies nicht einfach so möglich, man muss halt auch selber etwas ausprobieren. Ein guter SQL-Befehl um dies Bewergstelligen zu können ist EXPLAIN, dieser liefert Informationen über die Abfrage, indem man diesen einfach vor die SQL-Abfrage stellt (im Produktiv-System sollte man diesen entfernen). Dann sind Joins nicht unbedingt das beste Mittel um in MySQL-Abfrage zu erstellen.
"Unglücklicherweise ist die Bestimmung der optimalen Join-Reihenfolge eine der am wenigsten ausgeprägten Fähigkeiten von MySQL. Statt dieses Problem clever anzupacken, geht der Optimizer die Sache mit roher Gewalt an. Er probiert alle möglichen Kombinationen aus bevor er sich entscheidet." ~High Performance MySQL - Optimierung, Datensicherung, Replikation & Lastverteilung" von O'Reilly

Ich selber habe bisher kaum Joins benutzt, da ich dies immer über die Where-Klausel geregelt habe, nur in der Schule musste ich einmal mit Joins arbeiten, als wir Datenbanken durchkauten.

Was noch aus Deiner Tabelle 'keywords' raus kann ist eigentlich das Feld 'userid', da die Auswertung auch über die Tabelle 'anzeige' laufen kann.

Dann könntest Du folgendes ausprobieren:

• Indexe setzten auf entsprechende Abfragefelder, besonders wichtig hierbei sind die Fremdschlüssel (also die Schlüssel mit denen die Tabellen verknüpft werden). Als Faustregel gilt hier, dass bei einer Abfrage bei einen solchen Feld nur maximal 30% der Datensätze als Treffer auftauchen dürfen, sonst entscheidet sich MySQL ggf. dazu die komplette Tabelle zu durchsuchen, da MySQL meint, dies würde schneller sein.
• Machmal ist es besser eine Abfrage einfach in mehrere Abfragen aufzuteilen, damit die Ausführung dieser schneller geht.
• Auf das Feld 'keyword' in der Tabelle 'keywords' könnte ggf. ein Teilindex hilfreich sein, z. B. nur die ersten 4 Buchstaben des Feldes indizieren. MySQL muss dann immer noch die Tabelle durchsuchen, weiß aber dann aber schon mal ungefähr, an welchen Positionen dies sein könnte. Probieren geht hier halt über studieren.
• Eine weitere Normalizierung der Tabellen könnte helfen, auch das Aufteilen einiger Tabellen (bspw. für die Sessions eine eigene Tabelle).

Und noch ein kleiner Versuch von mir die Abfrage zu beschleunigen:

Stufe 1:

Stufe 2:

GROUP BY dürfte eigentlich nicht benötigt werden, da pro Anzeige eigentlich nicht das gleiche Keyword verwendet werden dürfte, da solltest Du am besten schon bei der Eingabe drauf achten und es dort kontrollieren.
Oder gibt es einen anderen Grund, weshalb Du hier die Keywords gruppierst? - Immerhin frisst das regelrecht Ressourcen.


Stufe 3:

Aufteilungen der Abfrage, so dass nicht immer der komplette Datensatz für jedes Keyword zusammengestellt und übertragen werden muss.


Stufe 4:

Das wäre meine Schreibweise der ersten Abfrage aus Stufe 3.



Zum Schluss sei noch gesagt, dass Du jede neue Abfrage mit EXPLAIN überprüfen solltest, und immer nur in sehr kleinen Schritten optimieren solltest: "Immer wieder testen nach einer Änderung, ob an der Abfrage oder der Tabelle (bspw. durch hinzufügen eines Indexes)".



MfG Sascha Ahlers

Hi,

also ich habe jetzt den ganzen tag Experimentiert und habe es jetzt so hier (dein vorschlag):

Bei meinen ganzen experimenten und versuchen , komme ich zu dem schluss das die Mysql Abfragen an sich jetzt nicht mehr das problem sind. Jedoch dauert das überprüfen der wörter im Text noch sehr lange.

Ich habe mir überlegt ob es nicht sinvoller wäre den Text ($nam) in einzelne Wörter zu teilen und nach diesen dann in der Keyword Tabelle zu suchen. Dabei ist natürlich das problem das die Keywords auch phrasen sein können. Aber man könnte es ja mit "like" machen und dann genauer prüfen.

Meint Ihr das würde schnelle gehen?

Habe mich jetzt noch ein wenig schlau gemacht und es scheint nur ein Syntax unterschied zu sein und nix anderes.

Wo findet man den? Habe gegoogelt und wie es aussieht läuft dieser im Hintergrund ab? Habe ich das richtig verstanden?

Wo du recht hast hast du recht

Vorallem das Group hat schon einiges an Performance rausgeholt. Habe es jetzt durch eine Function ersetzt.


Noch eine andere Frage: Weiß jemand wie man es bewerkstelligen kann das die seite als fertig geladen angezeigt wird auch wenn noch im Hintergrund die Auswertung stattfindet? Weil so ganz verstehe ich das auch nicht, php läuft ja Serverseitig ab und warum wird dann die Seite noch geladen?
Wird nicht der php code einfach an den Server Übertragen? Was macht der Browser in der Zeit? Eigentlich doch nix oder?

Und noch eine hinterher : Mir ist aufgefallen das der Firefox für die If abfrage einiges längerbraucht als der IE und das obwohl es sich um php handelt das ja auf dem server abläuft.

MFG

Jein, für das Ergebnis ist die Abfrageart gleich.

Jepp, der Optimierer läuft im Hintergrund. Man kann diesen aber über die Abfrage-Syntax beeinflussen (bzw. besser gesagt "eine Vorgehensweise aufzwingen").

Klar, mit AJAX, das ist ja auch mittlerweile IN.

Prinzipell sollte der Server erstmal alles verarbeiten und dann ausgeben (Standardeinstellungen von PHP). Doch mir selber kommt es öfters so vor, als würde PHP, trotz dieser Einstellung, nur die Ausgaben sammeln, und schon ab einen bestimmten Volumen ausgeben.



MfG Sascha Ahlers

Denke nicht, dass er das so meint der Borsti.

Eher im Stil von:

Und er will wahrscheinlich, dass nach dem "auswertung findet statt" beim Browser nicht mehr im "Laden" zustand ist sondern die Übertragung zum Browser abgeschlossen wird und nur noch auswertungs-code ausgeführt wird.

*ich das so verstehe*

Greets,
Joel

Du verstehen richtig.

Gibts da ne möglichkeit? Welche auch immer.

Noch offene Frage: (zur besseren Übersicht)

MFG

Hast Du das Ganze in jeden zweimal (kurz) hintereinander in einen Browser getestet und auch die Zeit dabei gemessen, wie lange PHP dafür braucht? - Wenn nicht, könnte es nur eine Täuschung sein, welche im Kern von PHP steckt (oder ggf. auch vom Browser her).

Ich vermute mal, dass es nicht geht, mir ist zumindestens nichts bekannt, wie man dem Browser sagt, dass er die Seite fertigeladen hat.

PHP mag serverseitig ausgeführt werden, doch muss der Server auch den Request abschließen, was er erst macht, nachdem das Script komplett durchgelaufen ist (entweder bis zum Ende der Datei oder bis zum Aufruf der Funktion 'exit').



MfG Sascha Ahlers