Typo3 - indexed search beschleunigen

Die Standardsuche von Typo3 ist die Indexed Search und kann gerade bei Webseiten mit vielen Unterseiten bzw. umfangreichen Inhalten (Text, PDFs usw.) sehr träge und langsam werden. Bevor man sich nun mit komplexen Volltextsuchen wie Elasticsearch beschäftigt, ist es ratsam, sich mit der Performance-Optimierung von Typo3's Indexed Search zu beschäftigen.

Wichtige Typo3-Tabellen für die Stoppwörter

Die zentrale Tabelle lautet "index_rel" und beinhaltet die Verknüpfung zwischen Typo3-Seiten (Spalte phash) und den darin enthaltenen Wörtern (Spalte wid). In der in diesem Artikel besprochenen Typo3-Installation enthält die Tabelle index_rel bei 1060 Seiten ca. 360.000 Einträge.

Um ein Wort als Stoppwort zu deklarieren, setzt man in der Tabelle "index_words" den Eintrag "is_stopword" auf den Wert 1. 

Stoppwörter im Typo3-Backend sind etwas schwer zu finden

Leider ist die Umsetzung im Typo3-Backend etwas umständlich, denn man kann sich die Stoppwörter immer nur für eine bestimmte Seite anzeigen. Dazu klickt man auf Info -> {SEITE} -> Indexsuche -> Title.

Stoppwörter - Auswertung auf Datenbankebene

Wie bereits erwähnt enthält die hier behandelte Typo3-Installtion 1060 Seiten, die indiziert werden. Um ein Gefühl dafür zu bekommen, wie stark der Suchindex durch "informationslose" Wörter aufgebläht wird, zeigt das folgende SQL-Statement. Es zeigt, auf wie vielen Seiten das Wort "und" wie oft vorkommt.

#
# SQL-Abfrage
# Auf wie vielen der 1060 Typo3-Seiten kommt das Wort "die" vor
#

SELECT 
 index_words.baseword AS 'suchwort', 
 index_rel.count AS 'vorkommen', 
 index_phash.data_page_id AS 'typo3 page id' 

FROM index_words

LEFT JOIN index_rel ON (index_words.wid = index_rel.wid)

LEFT JOIN index_phash ON (index_rel.phash = index_phash.phash)

WHERE index_words.baseword = 'die'

Zeige Datensätze 0 - 24 (892 insgesamt, Die Abfrage dauerte 0.0008 Sekunden.)
+----------+-----------+---------------+
| suchwort | vorkommen | typo3 page id |
+----------+-----------+---------------+
| die      |        48 |           417 |
| die      |        36 |           307 |
| die      |        20 |           408 |
| die      |        14 |           175 |
| die      |        26 |          1125 |
| die      |        15 |          1029 |
| die      |        14 |           323 |
| die      |        26 |           912 |
| die      |         7 |           729 |
| die      |         5 |           890 |
+----------+-----------+---------------+
[...]

Das Ergebnis zeigt, dass das Wort "und" auf 892 Seiten vorkommt. Für das Wort "und" landen also 892 Einträge in der Tabelle index_rel, die aber für gewöhnliche Suchanfragen nicht relevant sind. Ein Overhead, den man sich sparen kann. Machen wir einen weiteren Test und setzen die Wörter "der", "die", "das" als Stoppwort und suchen nun alle Index-Einträge heraus, die überflüssig sind.

#
# Die Wörter "der", "die", "das" wurden als Stoppwort deklariert.
# Wie viele Datensätze in index_rel sind Überflüssig
#

SELECT index_rel.* 

FROM index_rel

LEFT JOIN index_words ON (index_rel.wid = index_words.wid) 

WHERE index_words.is_stopword = '1';

 Datensätze 0 - 24 (2655 insgesamt, Die Abfrage dauerte 0.0034 Sekunden.) 
+--------+----------+-------+-------+------+-------+
| phash  | wid      | count | first | freq | flags |
+--------+----------+-------+-------+------+-------+
| 189646 | 44455709 |    33 |    83 | 5986 |     0 |
| 467995 | 44455709 |    15 |   255 | 4068 |     0 |
| 474594 | 44455709 |     8 |     0 | 2906 |     0 |
| 661156 | 44455709 |     4 |    72 | 1675 |     0 |
| 687778 | 44455709 |    25 |    42 | 6891 |     0 |
[...]

Das Ergebnis: 2.655 von 357.459 Einträge entfallen auf die Wörter "der", "die", "das". Anders formuliert: mit Ausschluss der drei Artikel werden 2.655 Index-Einträge obsolet. Dieses lässt sich aber nochmals ausbauen, indem wir häufig vorkommende "inhaltslose" Wörter ausfindig machen.  Wir suchen nun nach den am meisten vorkommenden Wörtern und sichten das Ergebnis:

#
# Welche Wörter kommen am meisten vor?
#

SELECT 
  index_words.baseword, 
  sum(index_rel.count) AS 'Summe'

FROM index_rel

LEFT JOIN index_words ON (index_words.wid = index_rel.wid)

GROUP BY index_rel.wid

ORDER BY sum(index_rel.count) DESC;

+----------+-------+
| baseword | Summe |
+----------+-------+
| die      | 23043 |
| der      | 19169 |
| und      | 15561 |
| â        | 11619 |
| in       |  9090 |
| ist      |  8271 |
| man      |  7988 |
| mit      |  7913 |
| den      |  7624 |
| das      |  7236 |
+----------+-------+

Die Sichtung der ersten Wörter ist sehr erfolgversprechend. "und", "ist", "man" sind typische Stoppwörter, die wir von der Indexsuche ausschließen. Diese gezeigte Auswertung hat aber einen kleinen Nachteil, da hier das absolute Vorkommen des Wortes berücksichtigt wird. Wenn man bspw. das Wort "michGibbetNicht" 1.000.000 mal auf eine einzelne Seite schreibt, landet das Wort in dieser Abfrage auf Platz 1, obwohl es (sehr wahrscheinlich) nur einen Eintrag in die index_rel-Tabelle schreibt. Aussagekräftiger daher ist die folgende Abfrage. Hierbei wird geprüft, ob ein einzelnes Wort in besonders vielen Seiten vorkommt.

#
# Welche Wörter kommen auf den meisten Seiten vor?
# Diese Abfrage ist besonders geeignet, um potentielle Stoppwörter zu finden
#

SELECT 
 index_words.baseword, COUNT(*) AS 'foundInPages'

FROM index_words

LEFT JOIN index_rel ON (index_words.wid = index_rel.wid)

GROUP BY index_rel.wid

ORDER BY COUNT(*) DESC;

+----------+--------------+
| baseword | foundInPages |
+----------+--------------+
| â | 1005 |
| und | 927 |
| der | 918 |
| die | 891 |
| mit | 886 |
| in | 882 |
| zu | 867 |
| auf | 852 |
| von | 851 |
| ist | 849 |
[...]

Deutlich wird das Prinzip an der Ergebnisliste. Das Wort "und" kommt in 927 von insgesamt 1006 indizierten Seiten vor. Es sorgt also für 927 Einträge in der Tabelle index_rel. Durch diese Abfrage ist es möglich, gezielt nach potentiellen Stoppwörtern zu suchen. Die Abfrage ist übrigens die Grundlage für die in diesem Artikel entstandene Stoppwortliste.

Stoppwörter setzen und Datenbank aufräumen

Nach dem theoretischen Teil wird es nun Zeit, die Erkenntnisse in die Praxis umzusetzen. Hierfür wird ein Update-Skript bereitgestellt, um...

• die Stoppwörter in der Tabelle index_words zu setzen
• alle Verknüpfungen aus der index_rel zu entfernen
• die Tabelle index_rel zu defragmentieren und zu optimieren

Download: indexedSearchSetStopwords.sql

Der erste Part in dem SQL-File ist selbsterklärend. Anhand der Stoppwortliste wird bei allen betroffenen Wörter in index_words das Flag is_stopword auf 1 gesetzt. Somit wird dieses Wort bei der Eingabe der Suche nicht mehr berücksichtigt. Sucht man als nach "AMD und Intel", so sucht Typo3 unter der Haube nach "AMD" + "Intel". 

Die Deklaration eines Stoppwortes verhindert aber nicht, dass das Stoppwort einen Eintrag in der Tabelle index_rel erzeugt. Es wird eben nur bei der Suchanfrage ignoriert. Es bleibt also der Overhead in der Tabelle index_rel, so dass große Datenmengen durchsucht werden müssen.

Aus diesem Grund räumt das SQL-Skript die Tabelle index_rel auf und schmeißt alle Stoppwörter raus

-- Stoppwörter aus index_rel entfernen
DELETE `index_rel`
FROM `index_rel` 
LEFT JOIN `index_words` ON (`index_rel`.wid = `index_words`.wid)
WHERE `index_words`.is_stopword = 1;

# 85924 Datensätze gelöscht. ( Die Abfrage dauerte 1.4343 Sekunden )

-- Tabelle defragmentieren
ALTER TABLE `index_rel` ENGINE = InnoDB;

-- Tabelle optimieren
OPTIMIZE TABLE `index_rel`;

Das Ergebnis kann sich sehen lassen. Nach der Säuberung sind nur noch ca. 270.000 statt 358.000 Einträge in der Tabelle index_rel. Die Datengröße schrumpft von 32 auf 16 MB.

Testszenario

Wir praktizieren einen (vielleicht etwas ungenauen) Vorher-/Nachher-Test. Wie lange benötigt der Webseite vor und nach der Aufräum-Aktion, um die Suchanfrage zu beantworten und die Response auszuliefern?

Das Ergebnis ist etwas ernüchternd, da der Geschwindigkeitsvorteil "nur" bei 19% liegt. Wie bereits angedeutet ist das Testsetup etwas ungenau, da die Messung der HTML-Auslieferung im Firebug von Firefox gemessen wurde. Hier können auch grundsätzlich die Internetanbindung oder die Auslastung des Webservers einen starken Einfluss haben. Um das Datenbank-Caching zu umgehen, wurde dieser in MySQL vor jeder Abfrage geleert und sogar MySQL + Apache neugestartet.

Nichtsdestotrotz kann das hier gezeigte Datenbank-Clean-Up dem einen oder anderen Typo3-Administrator weiterhelfen, eine inperformante Indexed-Search zu beschleunigen.

Seite zuletzt geändert am 22.05.2018 | Impressum | Datenschutzerklärung | Copyright © pc-erfahrung.de