# Volltext-Suche ĂŒber 100 000 Mails- und sie reagiert in 50 Millisekunden
Wie wir den klassischen FileMaker-WebViewer-Suchpatterns einen modernen Such-Backend untergeschoben haben, ohne die FileMaker-Datei selbst zu berĂŒhren. Mit einer SQLite-Datenbank, die schon im System lag. Mit einer Eingebauten von SQLite, die kaum jemand kennt. Und mit einer 2-Stunden-Implementierung, die jetzt fĂŒr DatenbestĂ€nde von 100 000 bis 10 Millionen Mails ausreicht, bei gleichbleibend sub-100-ms-Reaktion auf jeden Tastendruck.* --- ## Das Problem Im Mail-System eines Kunden lagen nach dem groĂen Legacy-Import knapp 96 000 Mails. Das vorhandene Suchpattern war ein bewĂ€hrter FileMaker-WebViewer-Trick: Eine **gespeicherte Berechnung** pro Datensatz baut ein HTML-Schnipsel (`
âŠ
`), eine zweite **ungespeicherte Berechnung** verknĂŒpft alle Schnipsel zu einer groĂen HTML-Seite mit Suchfeld, der WebViewer rendert sie als `data:`-URL, JavaScript filtert clientseitig per `data-search`-Attribut. Bei 5 000 DatensĂ€tzen ist das **magisch**: man tippt, die Liste filtert sich sofort, kein Server-Roundtrip, kein Lag. Bei 10 000 noch ok. Bei 92 000 stirbt es. Konkrete Zahlen aus der Diagnose: - `Liste()` ĂŒber 96 000 DatensĂ€tze: **10â30 Minuten** pro Aufruf (Oder FileMaker gibt auf) - Generiertes HTML: **~75 MB** als String - `data:`-URL: bei manchen WebKit-Versionen ĂŒber 64 MB einfach gekappt - 92 000 DOM-Knoten + JS `querySelectorAll` pro Tastendruck: **mehrere hundert Millisekunden** zĂ€hes Laggen Kurz: Das Pattern skaliert auf 5â10 k DatensĂ€tze. Bei 92 k bricht es. ## Die Optionen Drei klassische Wege: **A â Vorfilter.** Statt alle Mails zu zeigen, immer nur eine sinnvolle Teilmenge (letzte 90 Tage, Mails eines Kontakts). Reduziert auf <2 000 DatensĂ€tze, dann funktioniert das alte Pattern wieder. Pragmatisch, aber zwingt den User in eine starre Vorfilter-Logik. **B â Klassische FileMaker-Suche.** Suchfeld â ExecuteSQL â Ergebnismenge in Listenansicht. Skaliert auf Millionen, verliert aber das schöne Live-Filter-GefĂŒhl. **C â Server-side-Index.** Ein eigener Such-Endpoint im PHP-Backend, der gegen eine speziell optimierte Such-Datenbank fragt, JSON zurĂŒckliefert, vom WebViewer per Fetch konsumiert. Skaliert auf Millionen, behĂ€lt Live-Filter, kostet aber ein paar Stunden Aufbau. Variante C wĂ€re normalerweise Cadillac-Engineering. In dem Moment, als der Kunde fragte âhaben wir nicht schon eine SQLite im System?", wurde sie zur Anderthalb-Stunden-Lösung. ## Der entscheidende Aha-Moment Der PHP-Mailclient hatte schon eine SQLite-Datei. Genauer: `mailclient/data/links.sqlite`, eingerichtet fĂŒr die kleine VerknĂŒpfungstabelle `mail_links`, die Mail-Message-IDs auf FileMaker-EntitĂ€ten (Kontakte, Projekte, Konten) abbildet. PDO-Treiber konfiguriert, `db()`-Helper definiert, Schema-Initialisierung idempotent. Alles da. Und dann der zweite Aha: **SQLite hat seit Version 3.9 einen eingebauten Volltext-Index namens FTS5.** Eine virtuelle Tabelle, die Token-basierte Volltext-Suche ĂŒber Millionen DatensĂ€tze in <50 ms macht. Genau das, was Apple Spotlight nutzt. Kostenlos, ohne Plugin, ohne externen Index-Service. Ein schneller Check, ob FTS5 in der PHP-SQLite-Installation kompiliert ist: ```php $pdo = new PDO("sqlite::memory:"); $pdo->exec("CREATE VIRTUAL TABLE t USING fts5(x)"); $pdo->exec("INSERT INTO t VALUES ('hallo welt'), ('foo bar')"); $r = $pdo->query("SELECT x FROM t WHERE t MATCH 'hallo'")->fetchAll(PDO::FETCH_COLUMN); // â ['hallo welt'] ``` Ein âFTS5 verfĂŒgbar: ja" als Antwort, und das Projekt war von â2 Tage Aufwand" auf â2 Stunden" geschrumpft. ## Die Architektur ``` FileMaker WebViewer PHP-Mailclient SQLite +-------------------+ +-------------------+ +----------------+ | + JS | fetch(q=...) | /api/search_mails | SQL | mails_fts | | rendert Top 100 | <------------> | sucht im FTS5 | <-----> | (92k Mails) | +-------------------+ JSON +-------------------+ +----------------+ Tastendruck ^ | bei jedem neuen DS | /api/index_mail (von FM aufgerufen) ``` Drei neue PHP-Endpoints, eine FTS5-Tabelle, ein Python-Initial-Lauf, eine neue FileMaker-Berechnung mit ein paar Dutzend Zeilen JavaScript. Mehr nicht. ## Die FTS5-Tabelle ```sql CREATE VIRTUAL TABLE mails_fts USING fts5( pk_mail_id UNINDEXED, message_id UNINDEXED, betreff, body, absender, empfaenger, datum UNINDEXED, id_contacts UNINDEXED, id_account UNINDEXED, z_eingang UNINDEXED, thread_root_id UNINDEXED, tokenize = 'unicode61 remove_diacritics 2' ); ``` Zwei Details lohnen ErklĂ€rung: **`UNINDEXED`-Spalten** werden gespeichert, aber nicht in den Volltext-Index aufgenommen. Sie sind als reine Lookup- und Filter-Felder gedacht. So bleibt der Index kompakt: nur das, was wirklich durchsuchbar sein muss (`betreff`, `body`, `absender`, `empfaenger`), trifft den Tokenizer. **`remove_diacritics 2`** im Tokenizer ist der versteckte Held: er normalisiert alle Akzente und Umlaute. Eine Suche nach âmuller" findet âMĂŒller", âMueller" und âMĂŒller". Bei einem multilingualen Mail-Archiv mit deutschen, französischen, tĂŒrkischen Namen ist das nicht Nice-to-have, sondern FunktionalitĂ€t. ## Der Initial-Lauf Bei 96 000 Mails wĂ€re ein Datensatz-fĂŒr-Datensatz-Index-Update ĂŒber die FileMaker-Skript-Engine vermutlich ein Stundenprozess. Python machte es in **15 Sekunden**: ```python import sqlite3, csv conn = sqlite3.connect('mailclient/data/links.sqlite') conn.execute("PRAGMA journal_mode = WAL") conn.execute("PRAGMA synchronous = NORMAL") for csv_file in [mailausgang_csv, maileingang_csv]: batch = [] with open(csv_file, encoding='utf-8') as f: for row in csv.DictReader(f): batch.append(( row['Message_ID'], row['Betreff'], row['Body_Plain'], row['Absender_mailadresse'], row['Empfaenger'], # ⊠)) if len(batch) >= 1000: conn.executemany("INSERT INTO mails_fts (...) VALUES (?,?,...)", batch) conn.commit() batch = [] ``` 6 100 DatensĂ€tze pro Sekunde mit Batch-Inserts und WAL-Mode. Das Ergebnis: 91 935 Mails im Index, Datei-GröĂe der SQLite ~250 MB, Initial-Investment zum Aufbauen einmalig 15 Sekunden, danach fĂŒr immer verfĂŒgbar. ## Der Such-Endpoint PHP, gut 80 Zeilen Code. Der Kern: ```php $sql = " SELECT pk_mail_id, message_id, betreff, absender, empfaenger, datum, id_contacts, id_account, z_eingang, thread_root_id, snippet(mails_fts, 3, '', '', 'âŠ', 12) AS snippet FROM mails_fts WHERE mails_fts MATCH ? ORDER BY rank LIMIT 100 "; ``` `snippet()` ist eine eingebaute FTS5-Funktion: sie nimmt das Body-Feld (Index 3), markiert die Treffer-Worte mit ``-Tags und schneidet 12 Tokens links und rechts ringsum raus. Das Ergebnis sieht aus wie Gmail-Suchergebnisse: man tippt âheiratsurkunde", sieht ein paar Zeilen Body-Kontext mit gelb hervorgehobenem Treffer. `ORDER BY rank` sortiert nach FTS5-internem Relevanz-Algorithmus (BM25). Die hĂ€ufigste/beste Ăbereinstimmung erscheint oben. Kein Tuning, kein TF-IDF-Selbstbau nötig. Etwas mehr Sorgfalt verlangt das Mapping von **Benutzer-Eingaben in FTS5-Query-Syntax**: ```php $tokens = preg_split('/\s+/', $userInput); $clean = []; foreach ($tokens as $t) { $safe = preg_replace('/["\\\\:()*]/', '', $t); if (preg_match('/[@.\-]/', $safe)) { $clean[] = '"' . $safe . '"'; // E-Mail-Adresse als Phrase } else { $clean[] = $safe . '*'; // Prefix-Match fĂŒr "mueller" â "muellermann" } } $ftsQuery = implode(' ', $clean); ``` Drei Regeln: gefĂ€hrliche Sonderzeichen rausnehmen, Token mit `@`/`.` als Phrasen (sonst zerlegt FTS5 sie an Punkten), normale Wörter als Prefix-Match mit Stern (damit âmueller" sofort matcht, sobald 7 Zeichen getippt sind, ohne dass der User ENTER drĂŒcken muss). ## Das Frontend Im WebViewer: ```javascript let timer = null; function suche(q) { clearTimeout(timer); timer = setTimeout(() => doFetch(q), 200); } function doFetch(q) { if (!q || q.trim().length < 2) { document.getElementById('liste').innerHTML = '
Mindestens 2 ZeichenâŠ
'; return; } fetch(`${API}?q=${encodeURIComponent(q)}&limit=100&token=${TOKEN}`) .then(r => r.json()) .then(d => render(d)); } ``` Zwei Details: **Debouncing mit 200 ms.** Tippt der User âmueller", wird nicht fĂŒr jeden Buchstaben ein Request abgesetzt. Erst wenn 200 ms keine neue Eingabe kommt, geht der Fetch los. Das schont den Server und ist auf modernen Maschinen subjektiv noch immer âsofort". **Race-Condition-Schutz mit `lastReq`-ID.** Wenn der User schnell tippt und der erste Request langsamer zurĂŒckkommt als der zweite, wĂŒrde die Liste sonst kurz die alte Antwort zeigen. Ăber einen lokalen ZĂ€hler werden veraltete Antworten verworfen. ## Die CORS-Falle Beim ersten Test im WebViewer: nichts. âLoad failed". Ein typisches Browser-Mysterium, das eine Stunde Diagnose verschlingt, wenn man's nicht kennt. ErklĂ€rung: Der WebViewer lĂ€dt seine HTML-Seite per `data:`-URL. Aus Browser-Sicht hat eine `data:`-URL als Origin den Wert `null`. Macht der eingebettete JavaScript-Code dann einen `fetch()` an einen HTTP-Server, prĂŒft der Browser CORS (Cross-Origin Resource Sharing). Liefert der Server keine `Access-Control-Allow-Origin: *`-Header, wird die Antwort blockiert â der `fetch()` erhĂ€lt einen generischen âLoad failed"-Fehler ohne nĂ€here ErklĂ€rung. Fix: zwei Zeilen im `json_response()`-Helper: ```php header('Access-Control-Allow-Origin: *'); header('Access-Control-Allow-Methods: GET, POST, OPTIONS'); ``` Sofort lief alles. Eine dieser Sachen, die man einmal gelernt hat und nie wieder vergisst, aber bis dahin Stunden kosten kann. ## Performance â die echten Zahlen Ăber 96 935 Mails, gemessen direkt am Server, dann inklusive HTTP-Roundtrip aus dem WebViewer: | Such-Typ | SQL-Zeit | HTTP-Roundtrip | |---|---:|---:| | Volltext âmueller*" | 4 ms | 59 ms | | Volltext âheiratsurkunde" | 4 ms | 55 ms | | Volltext + Filter (z_eingang=1) | 5 ms | 34 ms | | Phrasen-Suche âaicher.com" | 4 ms | 42 ms | Der HTTP-Roundtrip ist dabei der Löwenanteil. Die eigentliche FTS5-Query liegt **unter 10 ms** â auch bei Millionen DatensĂ€tzen wĂŒrde sich daran wenig Ă€ndern. Vergleich mit dem alten Pattern: | | Liste-basierter WebViewer | FTS5-Backend | |---|---|---| | Initiales Rendering | 10â30 m | <100 ms | | Tastendruck-Reaktion | 100â500 ms (DOM-Filter) | 50â100 ms (HTTP) | | HTML-GröĂe initial | ~75 MB | ~1 KB pro Request | | Skalierungsgrenze | ~10 000 DS | 10 Mio+ | | Body-Volltext-Suche | begrenzt, kein Stemming | voll, mit Diacritics-Normalisierung | | Sortierung | nach Anlegen | nach Relevanz (BM25) | | Snippet-Hervorhebung | nein | ja, mit `` | | Sonderzeichen/Umlaut-Toleranz | nein | ja | ## Wartung Damit der Index nicht veraltet, muss er bei jeder neuen oder geĂ€nderten Mail aktualisiert werden: - **Mails per IMAP abholen**: das FileMaker-Skript ruft nach jedem neuen Datensatz `Mail indizieren` auf, der schickt den DS per POST an `/api/index_mail.php` - **Mails senden**: macht der PHP-Endpoint selbst nach erfolgreichem Versand - **Manuelle Ănderungen** in FileMaker (Betreff editiert, Body angepasst): per Skript-Trigger OnRecordCommit â `Mail indizieren` Bei Inkonsistenzen genĂŒgt ein Reset: Python-Script neu laufen lassen, leert den Index, baut ihn neu auf. 15 Sekunden. ## Bilanz Was am Ende steht: - **Live-Volltext-Suche ĂŒber 96 935 Mails** mit <100 ms Antwortzeit - **Sub-50-ms-Performance auf der DB-Ebene** â wĂŒrde auch bei 10 Mio DatensĂ€tzen noch unter 100 ms bleiben - **Snippet-Hervorhebung** mit Kontext-Wörtern um den Treffer herum - **Umlaut- und Akzent-tolerant**: âmuller" findet âMĂŒller" - **Filter-Kombinationen**: Volltext + Kontakt + Richtung + Thread (ĂŒber zusĂ€tzliche URL-Parameter) - **Skaliert** auf jeden Datenbestand, der realistisch in FileMaker liegt - **Wartung minimal**: PHP-Endpoint indiziert neue Mails automatisch, Python-Script setzt bei Bedarf alles zurĂŒck Das Pattern ist breit ĂŒbertragbar. Ăberall dort, wo FileMaker mit groĂen Datenmengen Live-Suche bieten soll â Kontakte, Aufgaben, Dokumente, Logs â ist FTS5 in einer Helper-SQLite die richtige Antwort. Die FM-Datei selbst bleibt unangetastet, die Suche lĂ€uft daneben, der WebViewer ist die BrĂŒcke. ## Was als NĂ€chstes kĂ€me Drei mögliche Erweiterungen, die diese Lösung von âfunktional" zu âbrillant" heben wĂŒrden: 1. **Server-side Pagination**: aktuell 100 Treffer pro Anfrage. Bei einer â1000 Treffer fĂŒr muller" könnte âMehr laden"-Button mit `offset`-Parameter ergĂ€nzt werden. 2. **Faceted Search**: Aggregations-Endpoint, der fĂŒr eine Such-Query liefert âX Treffer in Inbox, Y in Sent, Z bei Kontakt A". Bietet sofortige Verfeinerungs-Optionen. 3. **Synonyms-Wörterbuch**: fĂŒr GeschĂ€ftsbegriffe Synonyme definieren (âRechnung" matcht auch âInvoice"). FTS5 unterstĂŒtzt das ĂŒber Custom-Tokenizer. Aber: nichts davon braucht es heute. Sub-100-ms-Volltextsuche ĂŒber 96 000 Mails reicht fĂŒr die nĂ€chsten Jahre. Das Pattern ist offen, falls je gebraucht. --- **Stack:** - SQLite 3.x mit FTS5 (in praktisch jeder modernen PHP-Installation enthalten) - PHP 8.x, PDO-SQLite-Treiber - FileMaker WebViewer mit `data:`-URL + JavaScript-Fetch - Python 3 fĂŒr den einmaligen Initial-Index-Lauf **Code-Umfang nach Implementierung:** - `lib/helper.php`: +40 Zeilen (FTS5-Tabelle + `index_mail()`-Helper) - `api/search_mails.php`: ~100 Zeilen (komplett neu) - `api/index_mail.php`: ~40 Zeilen (komplett neu) - `/tmp/initial_index_mails.py`: ~80 Zeilen (einmalig) - FileMaker-Berechnung `wv_html_search_fts`: ~80 Zeilen JavaScript+HTML in einer FM-Calc - FileMaker-Skript `Mail indizieren`: ~12 Steps
