When MySQL LIKE queries slow down past 100K records, Elasticsearch is the answer.
Why Elasticsearch
MySQL LIKE queries are fundamentally limited: no tokenization, no synonyms, no fuzzy matching, and performance degrades linearly with data size. Elasticsearch is a distributed search engine built on Lucene, purpose-built for full-text search.
Performance Comparison
| Scenario | MySQL LIKE | Elasticsearch |
|---|---|---|
| 100K records | 200ms | 5ms |
| 1M records | 2,000ms | 15ms |
| Chinese tokenized search | Not supported | 5ms |
| Pinyin search | Not supported | 8ms |
Core Concepts
- Index: Like a database table
- Document: Like a table row, in JSON format
- Mapping: Like a table schema, defines field types and analyzers
- Analyzer: Tokenizer that determines how text is split and indexed
Spring Boot Integration
Spring Data Elasticsearch makes integration straightforward. Define your entity with @Document annotation, create a repository interface extending ElasticsearchRepository, and you get full CRUD + search operations.
Chinese Search Optimization
The default analyzer handles Chinese poorly. Install the IK analyzer plugin (ik_max_word mode) to properly tokenize Chinese text.
Data Sync Strategy
Two approaches:
- Dual write: Application writes to both MySQL and ES simultaneously. Simple but risks inconsistency.
- CDC via Canal: Listen to MySQL binlog, auto-sync to ES. Eventually consistent and more elegant.
When to Use ES
- E-commerce product search
- Blog/article full-text search
- Log analysis and retrieval
- Enterprise knowledge base search
When NOT to use ES: Simple CRUD with basic filtering. MySQL is fine. Don't add complexity you don't need.
Small team, big output. iDev builds web apps, AI solutions and custom systems with startup speed and enterprise quality. Based in Malaysia, serving Southeast Asia. Free consultation.
Top comments (0)