MoEVing

From MySQL to Search

Separating operational data from search by extracting and indexing it into Elasticsearch.

The Problem

The product’s operational data already lived in MySQL, but search required a different querying model. As retrieval needs grew, relying on the transactional database for search would make both responsibilities harder to manage efficiently.

The Constraint

Keep MySQL as Source

Optimize for Retrieval

Data Freshness

The operational database needed to remain the source of truth for product data.

Search needed its own structure and indexing behaviour, independent from transactional queries.

The search index needed to stay sufficiently synchronized with changes in MySQL.

  • Keep MySQL as SourceThe operational database needed to remain the source of truth for product data.
  • Optimize for RetrievalSearch needed its own structure and indexing behaviour, independent from transactional queries.
  • Data FreshnessThe search index needed to stay sufficiently synchronized with changes in MySQL.

The Options

Search Directly in MySQL

Not Chosen

Application-Level Search Logic

Not Chosen

Index Data in Elasticsearch

Chosen

Why We Didn't Choose It

Using MySQL for search would keep retrieval tightly coupled to the transactional database.

Benefit

Simplest architecture with no extra datastore.

Drawback

Search complexity and operational queries compete in the same system.

Decision Rationale

Why We Chose The Approach

From MySQL to a dedicated search indexMySQL remains the operational source of truth. Only searchable fields are extracted and indexed into Elasticsearch. The product sends search queries through a dedicated Search API, which queries Elasticsearch and returns search results. The product does not query Elasticsearch directly. Select a decision to highlight it.QUERYRESULTSMySQLSOURCE OF TRUTHSearchabledataElasticsearchINDEX · FILTER · SEARCHSearch APIProduct

The Result

Search became a dedicated system concern without adding retrieval pressure to the transactional database.

In Retrospect

If I were revisiting this architecture, I’d focus on three questions:

  1. How fresh does the search index need to be?
  2. What happens when indexing fails or falls behind?
  3. How do we detect drift between MySQL and Elasticsearch?