MySQL and Elasticsearch: Hybrid Search Architecture for High-Load Applications in 2026

Introduction: Why MySQL Alone Is Not Enough for Search in 2026

MySQL remains one of the most popular relational databases in 2026 — reliable, well-understood, with a rich ecosystem. But when it comes to full-text search in high-load applications, its built-in capabilities quickly hit a ceiling.

MySQL's built-in FULLTEXT index works through the InnoDB engine and supports basic search operations. However, it cannot rank results by relevance at the level of Elasticsearch, does not support fuzzy search, cannot handle synonyms, does not scale horizontally, and starts to degrade in performance at tens of millions of rows under search query load.

A hybrid MySQL + Elasticsearch setup is justified when several conditions are met simultaneously:

• Data volume exceeds 5–10 million records with active search queries.
• Relevance ranking, autocomplete, faceted search, or fuzzy matching is required.
• Search query load is hundreds of RPS or more.
• MySQL serves as the system of record (source of truth), and search is a secondary function.

In this article, we will walk through the architecture of such a system — from data synchronization to monitoring — with real configuration examples.

Architectural Overview: The Role of MySQL and Elasticsearch

The key principle of hybrid architecture: MySQL is the source of truth, Elasticsearch is the search layer. Data is always written to MySQL, and Elasticsearch receives it only through a synchronization mechanism. This provides ACID guarantees for write operations and horizontal scalability for read and search operations.

A typical request flow looks like this:

• Create, update, delete operations → MySQL.
• Search queries with text filtering → Elasticsearch.
• Point lookups by ID, aggregations with JOINs → MySQL.
• Faceted search, autocomplete, geo-search → Elasticsearch.

The application should not mix these layers: each component handles the task it is optimized for. Data in Elasticsearch is a projection of MySQL data, denormalized to meet search requirements.

Data Synchronization: Strategies and Comparison

Choosing a synchronization strategy is the most important architectural decision in this setup. Let's look at three main approaches.

CDC (Change Data Capture) with Debezium

Debezium reads MySQL's binary log (binlog) and publishes change events to Kafka. This is the most reliable and scalable approach for production environments.

Pros: minimal load on MySQL, precise tracking of every change, ability to replay events, low lag (seconds).

Cons: requires Kafka and Kafka Connect in the infrastructure, more complex to debug, requires binlog_format=ROW to be enabled in MySQL.

Logical Replication via Triggers or the Outbox Pattern

With each write to the main table, the application also writes an event to an outbox table. A separate worker reads this table and indexes changes into Elasticsearch.

Pros: requires no third-party tools, full control over transformation logic.

Cons: additional load on MySQL, risk of forgetting to add a record to the outbox when business logic changes, harder to guarantee exactly-once semantics.

Periodic (Batch) Sync

A scheduler runs a query like SELECT * FROM products WHERE updated_at > :last_sync and re-indexes changed records.

Pros: simple to implement, minimal dependencies.

Cons: lag from seconds to minutes, does not track deletions without soft-delete, may fall behind the stream of changes under heavy load.

For high-load systems, CDC with Debezium is recommended. Let's look at its setup in more detail.

Practical Example: Setting Up MySQL → Elasticsearch via Debezium

Suppose we have a products table in MySQL that needs to be indexed in Elasticsearch. Environment: MySQL 8.0, Kafka 3.x, Debezium 2.x, Elasticsearch 8.x.

Step 1: MySQL Configuration

Make sure the required parameters are enabled in my.cnf:

[mysqld]
server-id = 1
log_bin = /var/log/mysql/mysql-bin.log
binlog_format = ROW
binlog_row_image = FULL
expire_logs_days = 7
gtid_mode = ON
enforce_gtid_consistency = ON

Step 2: Debezium Connector Configuration

Create a JSON configuration for Kafka Connect:

{
  "name": "mysql-products-connector",
  "config": {
    "connector.class": "io.debezium.connector.mysql.MySqlConnector",
    "tasks.max": "1",
    "database.hostname": "mysql-host",
    "database.port": "3306",
    "database.user": "debezium",
    "database.password": "secret",
    "database.server.id": "184054",
    "topic.prefix": "myapp",
    "database.include.list": "shop",
    "table.include.list": "shop.products",
    "schema.history.internal.kafka.bootstrap.servers": "kafka:9092",
    "schema.history.internal.kafka.topic": "schema-changes.shop",
    "include.schema.changes": "true",
    "snapshot.mode": "initial",
    "transforms": "route",
    "transforms.route.type": "org.apache.kafka.connect.transforms.ReplaceField$Value",
    "transforms.route.whitelist": "id,name,description,price,category_id,updated_at"
  }
}

Step 3: Elasticsearch Sink Connector

Use the Kafka Connect Elasticsearch Sink to write events to the index:

{
  "name": "elasticsearch-products-sink",
  "config": {
    "connector.class": "io.confluent.connect.elasticsearch.ElasticsearchSinkConnector",
    "tasks.max": "2",
    "topics": "myapp.shop.products",
    "connection.url": "http://elasticsearch:9200",
    "type.name": "_doc",
    "key.ignore": "false",
    "schema.ignore": "true",
    "behavior.on.null.values": "DELETE",
    "transforms": "extractKey,unwrap",
    "transforms.extractKey.type": "org.apache.kafka.connect.transforms.ExtractField$Key",
    "transforms.extractKey.field": "id",
    "transforms.unwrap.type": "io.debezium.transforms.ExtractNewRecordState",
    "transforms.unwrap.drop.tombstones": "false",
    "transforms.unwrap.delete.handling.mode": "rewrite",
    "transforms.unwrap.add.fields": "op,ts_ms"
  }
}

Schema Mapping: Transforming the Relational Model into Documents

A relational model does not map directly to documents in a clean way. A search document must be denormalized: data from multiple tables is merged into a single document to avoid JOINs at search time.

Example: the products, categories, and brands tables in MySQL are combined into a single Elasticsearch document:

PUT /products
{
  "mappings": {
    "properties": {
      "id": { "type": "integer" },
      "name": {
        "type": "text",
        "analyzer": "english",
        "fields": {
          "keyword": { "type": "keyword" },
          "suggest": { "type": "completion" }
        }
      },
      "description": { "type": "text", "analyzer": "english" },
      "price": { "type": "scaled_float", "scaling_factor": 100 },
      "category": {
        "properties": {
          "id": { "type": "integer" },
          "name": { "type": "keyword" },
          "slug": { "type": "keyword" }
        }
      },
      "brand": {
        "properties": {
          "id": { "type": "integer" },
          "name": { "type": "keyword" }
        }
      },
      "tags": { "type": "keyword" },
      "is_active": { "type": "boolean" },
      "updated_at": { "type": "date" }
    }
  },
  "settings": {
    "number_of_shards": 3,
    "number_of_replicas": 1,
    "analysis": {
      "analyzer": {
        "english": {
          "type": "custom",
          "tokenizer": "standard",
          "filter": ["lowercase", "english_stop", "english_stemmer"]
        }
      },
      "filter": {
        "english_stop": { "type": "stop", "stopwords": "_english_" },
        "english_stemmer": { "type": "stemmer", "language": "english" }
      }
    }
  }
}

Key mapping rules: use keyword for fields used in aggregations and exact filtering. Use text with the appropriate analyzer for full-text search. For numeric ranges, use proper numeric types — not text.

Handling Data Divergence: Eventual Consistency and Dead Letter Queue

A hybrid architecture is inherently eventually consistent — there is a delay between writing to MySQL and the data appearing in Elasticsearch. This is expected, but it must be monitored.

Dead Letter Queue (DLQ)

Configure a DLQ in Kafka Connect to handle indexing errors. If a document fails to be written to Elasticsearch (for example, due to a mapping conflict), the message is routed to a separate topic for manual analysis:

"errors.tolerance": "all",
"errors.deadletterqueue.topic.name": "dlq-elasticsearch-products",
"errors.deadletterqueue.context.headers.enable": "true",
"errors.log.enable": "true",
"errors.log.include.messages": "true"

Sync Lag Monitoring

Track Consumer Group Lag in Kafka — the difference between the last written offset and the current consumer position. A lag of more than 1,000 messages under normal load is a signal to investigate.

Add an indexed_at field (indexing timestamp) and an updated_at field (time of change in MySQL) to every Elasticsearch document. The difference between them is the real synchronization lag for that specific record.

Reconciliation Job

Once a day, or when anomalies are detected, run a reconciliation job: export from MySQL the list of IDs with updated_at within the last N hours and compare with what exists in Elasticsearch. Force re-index any discrepancies.

Query Patterns: When to Use MySQL and When to Use Elasticsearch

A clear separation of responsibility between the two stores is the foundation of system performance.

Queries for Elasticsearch

• Full-text search across multiple fields with relevance ranking.
• Faceted filtering (category + price + brand + rating).
• Autocomplete and prefix search via the completion suggester.
• Fuzzy search (fuzziness: AUTO) for typo correction.
• Geo-search (geo_distance query).

Queries for MySQL

• Fetching a full record by ID after a search in Elasticsearch.
• Complex transactional operations spanning multiple tables.
• Financial and critical aggregations requiring ACID guarantees.
• Administrative queries with arbitrary JOINs.

Combined Queries

The "Search then Fetch" pattern: first retrieve a list of relevant IDs from Elasticsearch, then load full objects from MySQL using those IDs. It is important to limit the number of IDs fetched — no more than 1,000 at a time, otherwise the MySQL query WHERE id IN (...) starts to slow down.

-- After obtaining ids = [42, 17, 891, ...] from Elasticsearch
SELECT p.*, c.name as category_name, b.name as brand_name
FROM products p
JOIN categories c ON p.category_id = c.id
JOIN brands b ON p.brand_id = b.id
WHERE p.id IN (42, 17, 891)
ORDER BY FIELD(p.id, 42, 17, 891); -- preserve relevance order

Performance: Benchmarks and Tuning

Elasticsearch Tuning

For indexes under heavy write load, apply the following settings:

PUT /products/_settings
{
  "index": {
    "refresh_interval": "5s",
    "number_of_replicas": 0,
    "translog": {
      "durability": "async",
      "sync_interval": "5s"
    }
  }
}

After bulk indexing is complete, restore number_of_replicas: 1 and refresh_interval: 1s. Use the _bulk API for batch indexing — the optimal batch size is 500–2,000 documents when each document is up to 5 KB.

Approximate benchmarks on a 3-node cluster with 32 GB RAM each: indexing speed — 15,000–25,000 documents/sec, search query time with facets — p95 < 50 ms on an index of 50 million documents.

MySQL Optimization for Export

During initial indexing or reconciliation, follow these recommendations:

• Add a composite index on (updated_at, id) for incremental queries.
• Use cursor-based pagination via WHERE id > :last_id instead of LIMIT/OFFSET — OFFSET degrades on large tables.
• For a full snapshot, use mysqldump with --single-transaction or read data from a replica.
• Limit the number of columns in SELECT — do not pull fields into the index that are not needed for search.

Monitoring and Observability for the Hybrid System

A hybrid system requires monitoring at multiple levels simultaneously.

Kafka and Debezium Metrics

• kafka.consumer.lag — consumer lag per topic and partition.
• debezium.mysql.milliseconds_behind_master — binlog lag in milliseconds.
• Number of messages in the DLQ — should trend toward zero.

Elasticsearch Metrics

• Search query latency (p50, p95, p99) via Kibana or Prometheus ES Exporter.
• indexing_pressure.memory.total.primary_bytes — indexing memory pressure.
• GC pause time — should be under 200 ms; otherwise, JVM heap tuning is needed.
• Shard size — optimal range is 10–50 GB per shard.

Application Metrics

Add tracing (OpenTelemetry) for every search request with labels: data source (mysql or elasticsearch), execution time, number of results. This enables you to build SLOs for search availability and latency independently for each store.

Set up alerts for: sync lag > 30 seconds, DLQ growth, p95 search query degradation above threshold, Debezium connector failure (FAILED status in the Kafka Connect REST API).

Summary and Implementation Checklist

The MySQL + Elasticsearch hybrid architecture is a mature solution for high-load search systems in 2026. Below is a checklist for teams implementing this setup:

1. MySQL: binlog_format=ROW is enabled, a user with minimal privileges for Debezium is created (REPLICATION SLAVE, REPLICATION CLIENT), indexes on (updated_at, id) are added to exported tables.
2. Kafka: retention is configured sufficiently for replay (minimum 7 days), consumer lag monitoring is enabled.
3. Debezium: snapshot is configured for initial load, DLQ is set up, transformations (SMT) are verified.
4. Elasticsearch: mappings are created explicitly (not through dynamic mapping), an analyzer is configured for the data language, the correct number of shards and replicas is chosen.
5. Application: the "Search then Fetch" pattern is implemented, search goes only to Elasticsearch, writes go only to MySQL, a fallback to MySQL is added when Elasticsearch is unavailable for critical operations.
6. Observability: sync lag metrics are configured, alerts on DLQ and degradation are set up, request tracing is added.
7. Testing: integration tests are written for eventual consistency scenarios, recovery scenarios after Elasticsearch and Kafka failures are tested.

Start small: take one table, set up synchronization, verify stability, then scale to other entities. A properly built MySQL Elasticsearch synchronization delivers a 10–50x improvement in search performance compared to MySQL's FULLTEXT indexes, while preserving the reliability of the relational store as the source of truth.