A developer team paired OpenSearch with SQLite FTS5 and cut zero-result video searches from 11.4 percent to 2.1 percent, while keeping latency under 20 ms. Now users typing “blackpink jenny solo stag” see the correct “BLACKPINK Jennie SOLO stage” instead of an empty list.

Why the change was needed

Search logs from a video-hosting platform showed a recurring problem: a single typo in a Latin-script title could wipe out all matches. SQLite’s FTS5 extension, prized for its ability to match substrings in Chinese, Japanese and Korean (CJK) text, does not perform fuzzy matching. One misspelled character in a name or song title breaks the query entirely.

The existing pipeline treated SQLite as the sole index. It handled CJK queries well but offered no safety net for Latin-script typos. The team therefore looked for a complementary search engine that could provide typo tolerance without discarding the proven FTS5 layer.

How OpenSearch was added

OpenSearch runs as the front-line search service; SQLite remains the source of truth. The two systems run in parallel: OpenSearch receives the user query first, and if it responds quickly enough, its results are shown. If OpenSearch times out or errors, the request falls back to the SQLite FTS5 index. This “fail-safe” design guarantees that a network hiccup never leaves the search bar empty.

Multi-field mapping

Each video title is indexed three ways in OpenSearch:

  • title.std – processed by a standard analyzer with ASCII folding. This normalises accented characters and handles most Latin-script typos.
  • title.cjk – processed by a CJK analyzer that creates bigrams (two-character tokens). This preserves the substring-matching strength that FTS5 provides for Asian scripts.
  • title.keyword – stored unchanged for exact-match look-ups and sorting.

Separate fields let the query apply the right analysis to each script without mixing tokenisation strategies.

Boost tiers

Instead of a single monolithic query, the team built a tiered query that ranks results automatically:

  1. Exact phrase matches on title.keyword receive the highest boost, ensuring perfect matches dominate the list.
  2. CJK bigram matches on title.cjk get a medium boost, preserving the quality of Asian-language searches.
  3. Fuzzy Latin matches on title.std receive a lower boost, allowing typo-tolerant results to appear without eclipsing exact hits.

The tiered approach makes tuning straightforward: adjusting one boost value changes the relative importance of an entire class of matches.

Smart fuzziness

Fuzziness—allowing a limited number of character edits—applies only to the Latin field. The team disabled fuzziness for title.cjk because a single character change in CJK often alters meaning entirely. For Latin text the query uses OpenSearch’s AUTO fuzziness setting, which scales the allowed edit distance based on word length, striking a balance between tolerance and relevance.

Performance and fallback logic

The search routine wraps the OpenSearch call in a try-catch block:

  • If OpenSearch returns within 400 ms, its results are displayed.
  • If the call throws an exception or exceeds the timeout, the system immediately reruns the query against SQLite FTS5.

This ensures that network latency or service outages never degrade the user experience. Search latency stayed under 20 ms.

Measurable impact

  • Zero-result rates for Latin-script queries fell from 11.4 % to 2.1 %.
  • Search quality for CJK queries remained unchanged, confirming that the new CJK analyzer preserved the strengths of the original FTS5 index.
  • End-to-end latency stayed comfortably below the 20 ms target, meaning the added layer did not slow down the UI.

Lessons and trade-offs

  • Separate folding and fuzziness – Folding (normalising characters) and fuzziness (handling typos) address different problems. Keeping them on distinct fields avoids unintended interactions.
  • Do not treat the search index as the source of truth – SQLite remains the canonical store; OpenSearch is a derived, refreshable view. This prevents index drift and simplifies recovery after failures.
  • Boost tiers simplify tuning – Grouping related matches under a single boost factor reduces the number of parameters that need adjustment.

What to watch next

Thí nghiệm chứng minh rằng một lớp OpenSearch nhỏ gọn có thể cải thiện đáng kể khả năng xử lý lỗi chính tả cho các tiêu đề video đa ngôn ngữ mà không làm mất đi các khả năng xử lý CJK đã được chứng minh của SQLite FTS5. Đối với các nền tảng mà độ liên quan của kết quả tìm kiếm ảnh hưởng trực tiếp đến thời gian xem, sự cải thiện đó sẽ chuyển hóa thành một lợi thế hữu hình về trải nghiệm người dùng.