A user in Warsaw searches our catalog for "minecfart parkour fail" and gets zero results. The video exists — it's titled "Minecraft Parkour Fail Compilation" and it has 400k views this week. But our LIKE '%minecfart%' query returns nothing, the user bounces, and our bounce-rate dashboard ticks up another fraction of a percent. Multiply that by thousands of daily searches across a viral-video catalog where titles are full of slang, emoji, transliterated names, and outright misspellings, and you have a real revenue problem hiding behind a "minor" search bug.
I run search infrastructure at ViralVidVault, a GDPR-compliant viral video discovery platform for the European market. Our primary datastore is SQLite in WAL mode behind PHP 8.4 on LiteSpeed, fronted by Cloudflare. SQLite is genuinely great — it serves our metadata reads in microseconds. What it is not great at is fuzzy text matching. This article is the story of how we bolted OpenSearch onto that stack specifically to handle typo-tolerant title search, what the query DSL actually needs to look like, and how we kept the whole thing fast at the edge without shipping a single byte of PII to a third party.
Why SQLite FTS Was Not Enough
My first instinct was to avoid a new service entirely. SQLite ships with FTS5, a full-text search extension that is fast and requires zero extra infrastructure. So we tried it. Here is roughly what a naive title search looked like before any of this:
<?php
declare(strict_types=1);
final class TitleSearch
{
public function __construct(private readonly \PDO $db) {}
/** @return list<array{id:int,title:string}> */
public function search(string $term): array
{
// FTS5 virtual table: video_fts(title, description)
$stmt = $this->db->prepare(
'SELECT v.id, v.title
FROM video_fts f
JOIN videos v ON v.id = f.rowid
WHERE video_fts MATCH :q
ORDER BY rank
LIMIT 30'
);
// FTS5 needs a prefix token to do anything remotely fuzzy
$stmt->execute([':q' => $this->toMatchQuery($term)]);
return $stmt->fetchAll(\PDO::FETCH_ASSOC);
}
private function toMatchQuery(string $term): string
{
$tokens = preg_split('/\\s+/', trim($term)) ?: [];
// Best FTS5 can do generically is prefix matching: minec*
return implode(' ', array_map(static fn(string $t): string => $t . '*', $tokens));
}
}
FTS5 with prefix tokens handles "minec" → "minecraft" fine. What it fundamentally cannot do is edit-distance matching. "minecfart" is not a prefix of "minecraft" — it has a transposition and a substitution — so minecfart* matches nothing. You can bolt on spellfix1 (SQLite's Damerau-Levenshtein extension), but it operates on a separate vocabulary table, it is not compiled into most hosting-provider SQLite builds, and stitching it into ranked FTS results is fiddly. On LiteSpeed shared-ish hosting where I don't control the SQLite compile flags, spellfix1 was simply not available.
The deeper issue is that typo tolerance is a ranking problem, not a boolean one. We don't just want "does this fuzzy-match" — we want "of the videos that fuzzy-match, which are the closest AND the most popular right now." That is exactly what a proper search engine's scoring pipeline is built for. So OpenSearch it was.
Why OpenSearch and Not Elasticsearch
Quick note since people always ask. OpenSearch is the Apache-2.0 fork of Elasticsearch that the community and AWS maintained after Elastic changed its license in 2021. For our purposes the query DSL is nearly identical, but OpenSearch has two things I cared about: a permissive license with no ambiguity for a commercial European product, and self-hostable Docker images I can run on a small EU-region VPS. That last point matters enormously for GDPR — I never want search queries containing user intent leaving the EEA or landing in a US-controlled managed service. Self-hosting OpenSearch in Frankfurt keeps the data residency story clean.
Designing the Index Mapping
The single most important decision in fuzzy search is the analyzer and mapping, not the query. Get the mapping wrong and no amount of clever querying saves you. Here is the index we create for video titles:
PUT /videos
{
"settings": {
"number_of_shards": 1,
"number_of_replicas": 1,
"analysis": {
"filter": {
"vault_edge_ngram": {
"type": "edge_ngram",
"min_gram": 2,
"max_gram": 15
}
},
"analyzer": {
"title_index": {
"type": "custom",
"tokenizer": "standard",
"filter": ["lowercase", "asciifolding", "vault_edge_ngram"]
},
"title_search": {
"type": "custom",
"tokenizer": "standard",
"filter": ["lowercase", "asciifolding"]
}
}
}
},
"mappings": {
"properties": {
"title": {
"type": "text",
"analyzer": "title_index",
"search_analyzer": "title_search",
"fields": {
"raw": { "type": "keyword" },
"fuzzy": { "type": "text", "analyzer": "title_search" }
}
},
"views_7d": { "type": "long" },
"published_at": { "type": "date" },
"region": { "type": "keyword" }
}
}
}
There are three deliberate choices packed in here worth unpacking:
-
asciifoldingcollapses accented characters to their ASCII base. A German user searching "muller" should find "Müller", and a French title "Pokémon" should be reachable by typing "pokemon". For a European catalog this filter alone eliminates an entire class of "missing result" complaints. -
Separate index and search analyzers. We apply
edge_ngramonly at index time viatitle_index, but query with the plaintitle_searchanalyzer. If you ngram both sides you get an explosion of garbage matches. Indexing ngrams lets us match prefixes cheaply; searching without ngrams keeps the query terms whole so scoring stays sane. -
The
.fuzzysub-field is a plain analyzed text field with no ngrams. This is the field we run actual edit-distance fuzziness against, because Levenshtein fuzziness on top of ngram tokens produces nonsense. Multi-field mappings let us have both strategies on the same source string without duplicating storage in our app.
The edge_ngram field gives us instant "search as you type" prefix behavior, and the .fuzzy field gives us Damerau-Levenshtein typo correction. We combine them at query time.
Indexing From SQLite Into OpenSearch
SQLite stays the source of truth. OpenSearch is a derived read model — if the cluster falls over, we can rebuild the entire index from SQLite in a couple of minutes, and search degrades gracefully back to FTS5. That is a property I insist on: the search engine is never the system of record.
We bulk-index in batches using the OpenSearch _bulk API. Here is the indexer, again PHP 8.4, that pages through the SQLite videos table and ships documents:
<?php
declare(strict_types=1);
final class OpenSearchIndexer
{
private const BATCH = 500;
public function __construct(
private readonly \PDO $db,
private readonly string $endpoint, // https://os.internal.eu:9200
) {}
public function reindexAll(): int
{
$rows = $this->db->query(
'SELECT id, title, views_7d, published_at, region
FROM videos WHERE deleted_at IS NULL'
);
$buffer = '';
$count = 0;
$sent = 0;
foreach ($rows as $row) {
// action line + source line, newline-delimited JSON
$buffer .= json_encode([
'index' => ['_index' => 'videos', '_id' => (int) $row['id']],
], JSON_THROW_ON_ERROR) . "\n";
$buffer .= json_encode([
'title' => $row['title'],
'views_7d' => (int) $row['views_7d'],
'published_at' => $row['published_at'],
'region' => $row['region'],
], JSON_THROW_ON_ERROR) . "\n";
if (++$count % self::BATCH === 0) {
$this->flush($buffer);
$sent += self::BATCH;
$buffer = '';
}
}
if ($buffer !== '') {
$this->flush($buffer);
$sent += $count % self::BATCH;
}
return $sent;
}
private function flush(string $ndjson): void
{
$ch = curl_init($this->endpoint . '/_bulk');
curl_setopt_array($ch, [
CURLOPT_POST => true,
CURLOPT_POSTFIELDS => $ndjson,
CURLOPT_HTTPHEADER => ['Content-Type: application/x-ndjson'],
CURLOPT_RETURNTRANSFER => true,
CURLOPT_TIMEOUT => 30,
]);
$resp = curl_exec($ch);
$code = curl_getinfo($ch, CURLINFO_RESPONSE_CODE);
curl_close($ch);
if ($code !== 200 || $resp === false) {
throw new \RuntimeException("bulk index failed: HTTP {$code}");
}
// OpenSearch returns per-item errors even on HTTP 200 — check them
$decoded = json_decode((string) $resp, true, 512, JSON_THROW_ON_ERROR);
if (($decoded['errors'] ?? false) === true) {
error_log('partial bulk failure: ' . substr((string) $resp, 0, 500));
}
}
}
Two hard-won details. First, the _bulk body is newline-delimited JSON (NDJSON), not a JSON array, and it must end with a trailing newline — forget that and you get cryptic parse errors. Second, _bulk returns HTTP 200 even when individual documents fail, so you have to inspect the errors flag in the response body. I lost an afternoon to "successful" reindexes that had silently dropped a third of the corpus because of a mapping conflict on one field.
For incremental updates we don't run the full reindex. A cron job that already syncs fresh viral videos into SQLite simply pushes the changed IDs through the same flush() path. Full reindex runs nightly as a safety net to catch any drift.
The Fuzzy Query That Actually Works
Now the payoff. A good typo-tolerant query is not a single fuzzy clause — it is a bool query that combines several strategies and lets the scorer sort them out. Here is the query we build server-side and the PHP that assembles it:
<?php
declare(strict_types=1);
final class FuzzySearchQuery
{
/** @return array<string,mixed> */
public static function build(string $term, int $size = 24): array
{
return [
'size' => $size,
'query' => [
'bool' => [
'should' => [
// 1. Exact phrase match — highest signal, big boost
['match_phrase' => [
'title' => ['query' => $term, 'boost' => 5.0],
]],
// 2. Prefix / as-you-type via the edge_ngram field
['match' => [
'title' => ['query' => $term, 'boost' => 2.0],
]],
// 3. Typo tolerance via Damerau-Levenshtein
['match' => [
'title.fuzzy' => [
'query' => $term,
'fuzziness' => 'AUTO',
'prefix_length' => 1,
'max_expansions' => 50,
'boost' => 1.0,
],
]],
],
'minimum_should_match' => 1,
],
],
// Blend relevance with 7-day popularity so viral videos surface
'sort' => [
'_score',
['views_7d' => ['order' => 'desc']],
],
];
}
}
The magic is fuzziness: AUTO. It tells OpenSearch to scale the allowed edit distance by term length: 0 edits for 1-2 character terms, 1 edit for 3-5 characters, and 2 edits for anything longer. This is far better than a fixed fuzziness: 2, which would let short words match wildly unrelated tokens. "minecfart" (9 chars) is allowed 2 edits, and "minecraft" is exactly 2 edits away (one transposition counts as one Damerau operation, plus the substitution), so it matches and scores well.
prefix_length: 1 is a crucial performance guard: it requires the first character to match exactly before fuzziness kicks in. Real-world typos rarely happen on the first letter, and forcing an exact first char slashes the number of terms OpenSearch has to expand against, which keeps p99 latency down. max_expansions: 50 caps how many fuzzy variants a single term explodes into — without it, a common prefix on a large index can generate thousands of candidate terms and tank your query time.
Notice the should clauses with graduated boosts. An exact phrase match beats a prefix match beats a fuzzy match. So if someone types the title correctly they get the pristine result first; only when nothing exact matches do the fuzzy candidates carry the result set. Then the secondary sort on views_7d breaks ties toward whatever is actually going viral this week — which is the entire point of our product.
Wiring It Into PHP With a Graceful Fallback
The controller ties it together and, critically, falls back to SQLite FTS5 if OpenSearch is unreachable. Search must never 500 just because a downstream service is having a bad day.
<?php
declare(strict_types=1);
final class SearchController
{
public function __construct(
private readonly OpenSearchClient $os,
private readonly TitleSearch $sqliteFallback,
) {}
/** @return list<array<string,mixed>> */
public function handle(string $term): array
{
$term = trim(mb_substr($term, 0, 80)); // clamp input length
if ($term === '') {
return [];
}
try {
$body = FuzzySearchQuery::build($term);
$resp = $this->os->search('videos', $body, timeoutMs: 250);
return array_map(
static fn(array $hit): array => [
'id' => (int) $hit['_id'],
'title' => $hit['_source']['title'],
'score' => $hit['_score'],
],
$resp['hits']['hits'] ?? []
);
} catch (\Throwable $e) {
error_log('opensearch down, falling back to FTS5: ' . $e->getMessage());
return $this->sqliteFallback->search($term);
}
}
}
The 250ms timeout is deliberate. If OpenSearch can't answer in a quarter second, degraded FTS5 results now beat perfect results in two seconds. Users perceive slow search as broken search.
Caching at the Edge Without Leaking PII
Here is where GDPR intersects with performance. Search query strings are user-generated content and can absolutely contain personal data — people search for names, usernames, and worse. So our caching rules are strict: we cache popular, non-personal query results at the edge, and we never log raw query strings alongside identifiers.
We use a Cloudflare Worker to cache the head of the query distribution. A small fraction of queries ("funny cat", "world cup goal") make up the majority of traffic; caching those at the edge takes enormous load off the origin and the OpenSearch cluster.
export default {
async fetch(request, env, ctx) {
const url = new URL(request.url);
const q = (url.searchParams.get('q') || '').trim().toLowerCase();
// Only cache short, high-frequency, non-sensitive queries.
// Long or unusual queries bypass the cache and hit origin directly,
// so we never persist niche/personal search terms at the edge.
const cacheable = q.length > 0 && q.length <= 24 && /^[a-z0-9 ]+$/.test(q);
if (!cacheable) {
return fetch(request);
}
const cacheKey = new Request(`https://cache.internal/search?q=${encodeURIComponent(q)}`);
const cache = caches.default;
let response = await cache.match(cacheKey);
if (response) {
return response; // edge hit — origin never sees this query
}
response = await fetch(request);
if (response.status === 200) {
const cloned = new Response(response.body, response);
cloned.headers.set('Cache-Control', 'public, max-age=300');
ctx.waitUntil(cache.put(cacheKey, cloned.clone()));
response = cloned;
}
return response;
},
};
The /^[a-z0-9 ]+$/ guard is doing double duty. It restricts caching to simple alphanumeric queries — which are exactly the high-frequency, low-sensitivity ones — and it means anything containing an @, a dot, or unusual characters (the shape of emails, handles, and niche personal searches) bypasses the cache entirely and never gets persisted at the edge. Combined with a 5-minute TTL, we keep viral-trend results fresh while shielding the origin from the thundering herd every time a video blows up. Our OpenSearch cluster went from handling every single keystroke-triggered search to handling only the long-tail misses.
Results and What I'd Do Differently
After rolling this out across our European regions, the numbers that mattered:
- Zero-result searches dropped from about 11% to under 2%. The remaining 2% are genuinely absent content, not typos.
- Median search latency stayed under 40ms thanks to edge caching absorbing the popular head of the distribution.
- The OpenSearch cluster runs comfortably on a single 4GB EU-region node with one replica, because SQLite still absorbs all the exact-ID lookups and OpenSearch only handles fuzzy text.
Things I would do differently if starting over: I'd introduce the .fuzzy sub-field from day one instead of retrofitting it, because reindexing 2M documents to add a field is annoying even when it's fast. I'd also set up the search-analyzer/index-analyzer split before loading any data — I initially ngram-analyzed both sides and spent a day confused about why every query returned half the catalog.
The broader lesson is architectural. OpenSearch didn't replace SQLite; it sits beside it as a specialized, rebuildable read model for exactly one job it's good at. SQLite remains the source of truth, the fallback, and the fast path for everything that isn't fuzzy text. Keeping that boundary crisp is what let us add real typo tolerance without turning a simple, reliable PHP-on-LiteSpeed stack into a fragile distributed system. And keeping the whole search path inside the EEA meant we got all of that without a single GDPR asterisk.
If your search is still doing LIKE '%term%' and quietly losing users to typos, a small self-hosted OpenSearch node with a carefully chosen mapping is one of the highest-leverage changes you can ship.
Top comments (0)