Ich hatte auf meiner „Erwachsenen Videos Suchmaschine“ poppler.biz immer wieder mal Performanceprobleme bei verschiedenen Such abfragen und hab angefangen rum zu tüfteln, wie ich die Performance noch verbessern kann.
Angefangen von einer einfachen SQL Abfrage wie
SELECT url,title FROM tabelle force index (Z01) WHERE title LIKE ‚%Amateur%‘ OR description LIKE ‚%Amateur%‘ ORDER BY link_id DESC LIMIT 0,12;
die relativ schnell ziemlich langsam wurde mit einer SQL Zeit von etwa 22 Sekunden, als mehr als 11 Millionen Einträge in der Tabelle vorhanden waren, weiter zu einer Abfrage über einen
Fulltext Index mit DB Engine Aria
SELECT url,title FROM tabelle WHERE MATCH (title,description) AGAINST (‚+*Amateur*‘ in boolean mode) ORDER BY id DESC LIMIT 0,12;
Welche dann deutlich schneller war, mit etwa 10 Sekunden für eine Query.
Jetzt hätte ich weiter mit Joins weiter optimieren können, um die Abfrage bzw. die Anzahl der Zeilen zum Sortieren zu verringern.
Aber dann hab ich mir die Open Source Search Engine SPHINX angesehen.
Nach ein bisschen einlesen in die Konfiguration, hab ich dann auch recht schnell den Index mit Sphinx aus den Daten der MySQL Tabelle angelegt.
Zu Sphinx verbindet man sich dann ähnlich wie zu MySQL, nur über einen anderen Port zb. mit dem Befehl
mysql -h0 -P 9306 „DBNAME“
Und die SQL Syntax sieht so aus
SELECT url,title FROM SPHINXINDEXNAME where MATCH (‚@(title,description) *Amateur*‘) ORDER BY id DESC LIMIT 0,12;
Und die Dauer der Query ist hier nur noch 1,14 Sekunden lang.
Das ist eine deutlich Verbesserung gegenüber der MySQL Fulltext Index Suche.
In den PHP Files muss natürlich der DB Connect String angepasst werden wie etwa
$sphinxconnect = mysqli_connect(„127.0.0.1“,$dbuser,$dbpass, $dbname, 9306);
Und entsprechend dann die SQL Syntax für SPHINX Abfragen.
$sql = „SELECT url,title FROM SPHINXINDEXNAME where MATCH (‚@(title,description) *“.$SUCHPARAMETER.“*‘) ORDER BY id DESC LIMIT 0,12;“;
$raw_results = mysqli_query($sphinxconnect, $sql);
…
Die Experience für die Webseitenbesucher ist damit deutlich besser. Ich muss mich aber noch hinsetzten und analysieren, warum manche Suchwörter deutlich schneller ist als andere.
Das Suchwort „Amateur“ wie oben als Beispiel angegeben ist eines der eher langsameren.
Wenn man es mehrmals hintereinander abfragt, geht sie Query Zeit unter eine Sekunde.
Das könnte man noch mit mehr Speicherzuweisung optimieren. Damit Queries möglichst lange im Cache bleiben.
Wenn man aber nach „Hentai“ sucht, dauert eine Query nur 0.11 Sekunden.
Gut, es gibt hier deutlich weniger Ergebnisse und daher eine geringer Datenmenge zum sortieren.
Dennoch möchte ich die Abfragen, für Wörter die sehr häufig vorkommen, noch tunen, ohne das am Ende das Ergebnis abgekürzt wird.
Um die ORDER BY DESC Klausel kommen ich leider nicht herum, da ich die neuesten der ID nach zuerst anzeigen möchte.
Meine Empfehlung ist auf jeden Fall, wenn Ihr große Tabellen mit Millionen von Einträgen habt und die Suche darin nicht so performt wie ihr das gerne hättet, dann schaut euch mal SPHINX an.
Dokus zur Installation gibt es im Netz genug, für diverse Linux Distributionen, MacOS oder Windows.
Wer Fragen zu meiner Installation oder Konfiguration hat, kann gerne hier Kommentieren oder mich kontaktieren.