Case Study · Search

Search Infrastructure: From SQL LIKE to Elasticsearch at 100× Speed

Replaced legacy SQL search with a typo-tolerant, autosuggest-powered Elasticsearch layer across thousands of SaaS products.

Role: SDE-III · Team: 6-engineer pod · Stack: Elasticsearch · Node.js · RabbitMQ · Redis · MySQL

Indexing pipeline: product writes in MySQL emit events through RabbitMQ to a denormalising indexer that keeps Elasticsearch in near-real-time sync. Query path is cached in Redis.

The Problem

Search on the marketplace was a SQL LIKE query against the products table. As the catalog grew into the thousands, average search response time crossed multi-second territory, autocomplete didn't exist, and typos returned empty results — directly hurting conversion.

The deeper issue was that we'd outgrown what relational text matching could do. We needed relevance, not pattern matching. We needed forgiveness for typos, prefix matching for autosuggest, and a way to bias results by popularity and personalization. Layering that on top of LIKE was an exercise in pain.

The Solution

I designed and built an Elasticsearch-backed search service with four moving parts:

Denormalised product index synced from MySQL via a queue-driven pipeline — writes emit events, an indexer consumes and updates Elasticsearch in near-real-time.
Autosuggest using completion suggesters — prefix matching on a curated set of fields, so the dropdown lights up on the first keystroke.
Autocorrect via fuzzy matching with edit-distance tuning — typos no longer return empty result pages.
A custom ranking algorithm blending text relevance, popularity signals, and personalisation. The ranking is the part that moves the conversion number, not the speed.

Tech Stack

Elasticsearch
Node.js
MySQL
RabbitMQ
Redis

My Specific Contribution

Index schema. Field selection, analysers, mappings, and the denormalisation strategy.
Sync pipeline. The MySQL → RabbitMQ → indexer → Elasticsearch flow, including replay/backfill paths.
Query DSL. Hand-tuned multi-match, function score, and suggester queries.
Ranking algorithm. Blended scoring across relevance, popularity, and personalisation.
Rollout plan. Dark-launch behind a flag, side-by-side comparison with the SQL path, then gradual cutover.
Performance benchmarking. p50/p95/p99 against representative query mixes.

Impact

100×

Faster search response at catalog scale — multi-second SQL to sub-second Elasticsearch.

↑ CR

Measurable lift in search-driven conversion rate, attributable to the new ranking algorithm.

1 → N

Foundational infrastructure now powers multiple product surfaces.

Visuals

p95 search latency before vs after — the SQL LIKE path's tail disappears under Elasticsearch.

Live autocomplete and typo tolerance — neither existed in the SQL path.

Lessons

Speed was the easy win. The real value is in the ranking — which is also the part that never feels "done". A search system isn't a one-shot project; it's a living surface that you tune in response to what users actually click on. Build the feedback loop early.