Key Facts
A recent technical report on Juejin (稀土掘金) tested MySQL’s native ngram full-text search against LIKE searches across 500,000 rows of Chinese content on a 1-core, 2GB server. Key hard facts: MySQL ngram parser has been available since 5.7.6 (documented in 8.0 manual section 14.9.8), requires only WITH PARSER ngram suffix to enable, and incurs significant structural costs—index creation rebuilds the clustered index and increases storage. On a 500,000-row table, data_length grew from 213MB to 311MB; adding the index took 52.8 seconds on a 100,000-row table.
- No-match queries: LIKE 886ms → ngram natural/boolean mode 0ms (LIMIT 20)
- 2-character frequent term “缓存” (cache): LIKE 684ms → natural mode 150ms (4.5x faster), boolean mode 215ms
- 4-character phrase “消息队列” (message queue): LIKE 785ms → natural mode 323ms, boolean mode 1710ms (2.2x slower than LIKE)
Performance Surprises and Contradictions
The most counterintuitive finding appeared in long-phrase queries: Boolean mode search for the 4-character phrase “消息队列” took 1710ms, 2.2x slower than LIKE at 785ms, and 5.3x slower than natural language mode at 323ms. This occurs because Boolean mode converts Chinese queries into ngram phrase searches—first retrieving all documents containing ngram fragments (消息, 息队, 队列), then verifying consecutive occurrence position-by-position. With 160,000 rows matching out of 500,000, each required position verification exceeded the cost of a simple table scan on single-core hardware.
Another major issue is recall inflation: testing with “数据库连接池” (database connection pool), both LIKE and Boolean mode returned exactly 196,892 matches. Natural language mode (the default), however, returned 379,058 results—a 92% over-request. Manual verification found 182,166 documents (48%) contained none of the complete phrase, only matching individual fragments like “数据” (data) or “库” (library).
A lesser-known limitation: single-character queries fail entirely. With ngram_token_size=2 by default, no single-character tokens exist in the index. Searching just “缓” returns zero results. Changing the parameter requires server restart and affects all ngram indexes in the database.
Scenario Comparison
| Query Type | LIKE Full Scan | ngram Natural Mode | ngram Boolean Mode |
|---|---|---|---|
| No-match (long) | 886ms | 0ms | 0ms |
| 2-char frequent | 684ms | 150ms | 215ms |
| 4-char phrase | 785ms | 323ms | 1710ms (2.2x slower) |
| Single char query | 253,889 hits | 0 | 0 |
| Recall error rate | 0% | 48% | 0% |
Note: Test environment: 1-core/2GB server, 500,000 Chinese rows, LIMIT 20 pagination; boolean mode with quoted queries matches LIKE exactly.
Implementation Recommendations
Ready to deploy now if:
- Backend content search box in admin systems
- Data scale ≤500,000 rows
- Queries primarily use 2-4 character whole words
- Exact matching is acceptable
Mandatory guidelines:
- Always wrap queries in double quotes for Boolean mode (matches LIKE exactly)
- Avoid natural language mode (default) due to 48% recall inflation
Wait or choose Elasticsearch if:
- Search is a primary user-facing feature with queries >4 characters
- Single-character search needed (e.g., searching surnames like “张”)
- High precision required, no tolerance for partial matches
- Memory ≤2GB and long Boolean queries possible (OOM killer triggered in tests)
In Conclusion
MySQL’s native ngram index fills a gap for Chinese search infrastructure—but its semantic processing differs significantly from English full-text search. For low-frequency backend tools like ticket systems, one ALTER TABLE ADD FULLTEXT beats maintaining a separate ES cluster. Yet for user-facing high-frequency search with long-tail queries or complex relevance ranking, ES’s optimized inverted index and proper tokenization remain superior. The choice hinges not on cost but whether query patterns align with technological constraints.