Why Use ElasticSearch over MySQL? Key Differences for Interviews

Technical Comparison

MySQL (B+Tree index) stores rows in ordered index structures. Equality and range queries run in O(log n) time, but a query such as

SELECT * FROM product WHERE title LIKE '%phone%';

places the wildcard at the front, causing the optimizer to discard the index and perform a full‑table scan. Even with a covering secondary index the entire tree is scanned, which becomes prohibitive on tens of millions of rows. A prefix‑only pattern ( LIKE 'phone%') can use the index, but real‑world searches rarely have a fixed prefix.

ElasticSearch (Inverted index) builds on Apache Lucene. During indexing each term is extracted (e.g., by the ik_max_word tokenizer) and a postings list of document IDs is created. At query time a term lookup directly returns the matching documents, turning full‑text search into a millisecond operation.

Concept Mapping (ES ↔ MySQL)

Index ↔ Table (valid for ES 7.x and later)

Document ↔ Row

Field ↔ Column

Mapping ↔ Schema

DSL query (JSON) ↔ SQL

ES 6.x and earlier also had a type concept that mapped to a table; the type API was deprecated in ES 7.0 and removed in 8.0.

ES Write Flow – Near‑Real‑Time

Writes go through three steps:

Write to memory : data lands in the index buffer and the translog (similar to MySQL redo log). At this point the document is not searchable.

Refresh (default 1 s) : the buffer is flushed to a new Lucene segment, making the data searchable. The refresh_interval can be tuned, but a too‑small interval creates many tiny segments and hurts performance.

Flush : the segment is persisted to disk and the translog is cleared.

MySQL makes data visible immediately after transaction commit, so ES is fast for search but weaker for real‑time visibility and ACID guarantees.

Scenarios Where ES Is Not Suitable

Strong‑transaction workloads : ES lacks true ACID transactions; cross‑document rollbacks are impossible. Use MySQL for account deductions, order state changes, etc.

Frequent updates : ES segments are immutable; an update becomes a delete‑plus‑reindex operation, causing write amplification (e.g., inventory counters).

Complex multi‑table joins : ES join (nested/parent‑child) has severe performance pitfalls; relational databases handle ER relationships better.

Memory‑intensive workloads : ES requires substantial JVM heap and OS page cache; small projects with modest data may find it overkill.

Production Architecture – MySQL + ES (Golden Pair)

A typical double‑store pattern:

MySQL is the source of truth ; all write operations go to MySQL first, guaranteeing durability and transaction integrity.

Canal (or Flink CDC) monitors the MySQL binlog, pushes change events to a message queue.

A consumer reads from the queue and writes the changes to ES, decoupling business code from synchronization logic.

Search requests are routed to ES; normal CRUD operations remain on MySQL.

The message queue provides throttling, retry, and compensation mechanisms to ensure eventual consistency.

In an e‑commerce project with >50 million products, this architecture kept search latency around a few hundred milliseconds.

High‑Frequency Interview Follow‑Ups

Why is ES near‑real‑time, not real‑time? Because writes first reside in the index buffer and become searchable only after a refresh (default 1 s). Adjusting refresh_interval reduces latency but creates many small segments, degrading overall performance.

How to guarantee data consistency between MySQL and ES? The mainstream solution is asynchronous sync via Binlog + Canal/Flink CDC + MQ, which provides eventual consistency with retry and compensation tasks. Dual‑write (writing both systems in business code) is discouraged due to lack of transaction guarantees.

Explain the ES write flow. Buffer → translog → refresh → segment → flush. Interviewers may probe the index.translog.durability setting.

How to handle deep pagination in ES? The default from+size is limited by max_result_window (default 10 000). Use search_after or the Scroll API for deeper pages.

Common Interview Variants

Explain how the ES inverted index works.

Describe data‑sync schemes between ES and MySQL and their trade‑offs.

When should ES not be used?

What are the purposes of ES refresh, flush, and merge?

Memory Mnemonics

MySQL stores, ES searches – MySQL guarantees persistence and ACID; ES provides fast search and relevance scoring.

Forward index finds words, inverted index finds documents – MySQL follows the document→word path; ES reverses it to word→document.

Summary

ElasticSearch replaces B+Tree with an inverted index to achieve lightning‑fast full‑text search, at the cost of weaker transaction guarantees and near‑real‑time visibility. MySQL provides reliable storage, ACID transactions, and efficient relational queries but cannot handle fuzzy or large‑scale text search efficiently. In production the recommended pattern is a double‑store architecture: MySQL as the primary data source and ES as the search engine, synchronized asynchronously via Binlog + Canal (or Flink CDC) and a message queue. Mastering the differences between inverted index and B+Tree, as well as ES’s near‑real‑time write mechanism, is essential for answering this interview topic.