📈 MySQL Query Expansion & Ngram Parser – Boost Full-Text Search Precision

---

🧲 Introduction – Why Use Query Expansion & Ngram Parser?

MySQL’s full-text search is powerful, but it can be further enhanced with:

• 🧠 Query Expansion – Improves recall by searching for related terms
• 🧩 Ngram Full-Text Parser – Breaks text into substrings (n-grams) to enable language-agnostic and CJK-compatible search (Chinese, Japanese, Korean)

Together, these features provide smarter and more inclusive search capabilities, especially in multilingual or short-text datasets.

🎯 In this guide, you’ll learn:

• How WITH QUERY EXPANSION enhances search
• What the Ngram parser is and when to use it
• Syntax examples and configuration
• Best practices and limitations

---

🧠 1. MySQL Query Expansion – Discover Related Terms

Query expansion uses relevance feedback to automatically broaden search terms. It works in natural language mode only.

---

🔹 Syntax

SELECT * FROM articles
WHERE MATCH(title, body)
AGAINST('database performance' WITH QUERY EXPANSION);

---

🔹 How It Works

1. MySQL runs the original query.
2. It selects the top-matching rows.
3. It extracts the most relevant keywords from those results.
4. It re-runs the query using the original + related keywords.

---

🔍 Example

AGAINST('climate change' WITH QUERY EXPANSION)

✅ Expands to include terms like global warming, environment, etc.

---

⚠️ Notes

Limitation	Detail
Only works in Natural Mode	Not available in Boolean Mode
Results may be less specific	Sacrifices precision for recall
Requires FULLTEXT index	On at least one searchable column

---

🧩 2. MySQL Ngram Full-Text Parser – For Multilingual Search

🔹 What Is It?

The Ngram parser breaks strings into substrings of length N (default: 2 or 3), allowing:

• ✅ CJK (Chinese, Japanese, Korean) search
• ✅ Partial matching for short words
• ✅ Improved keyword discovery in non-spaced languages

---

🔹 Enable Ngram Parser

CREATE TABLE posts (
  id INT PRIMARY KEY,
  content TEXT,
  FULLTEXT(content) WITH PARSER ngram
) ENGINE=InnoDB;

---

🔹 Customize Ngram Token Size

[mysqld]
ngram_token_size = 2

• Minimum: 1
• Maximum: 10
• Default: 2 for InnoDB

🔁 Requires server restart and reindexing

---

🔹 Example – CJK Text Search

INSERT INTO posts(content) VALUES ('欢迎使用MySQL');

SELECT * FROM posts
WHERE MATCH(content) AGAINST('MySQL');

✅ Match works even without exact token, thanks to Ngram slicing.

---

🔍 3. Comparison Table – Query Expansion vs Ngram

Feature	Query Expansion	Ngram Fulltext Parser
Use Case	Broaden search scope	Enable substring/CJK search
Index Required	✅ Yes (standard FULLTEXT)	✅ Yes (with WITH PARSER ngram)
Compatible with	Natural Mode only	Both Natural & Boolean modes
Target Language	English / Latin alphabet	CJK (Chinese, Japanese, Korean)
Keyword discovery	Based on top rows	Based on N-gram slices
Precision	Medium (more recall)	High (find partial terms)
Configuration needed	❌ No	✅ Yes (for ngram_token_size)

---

🚀 Real-World Use Cases

Scenario	Feature to Use	Why
Improve search recall in blog engine	Query Expansion	Finds related, co-occurring terms
Search Chinese article titles	Ngram Parser	Breaks characters into searchable units
Short keyword search (e.g., SKUs)	Ngram Parser	Matches AB12 inside long text
Educational Q&A platform	Both	Broad relevance + substring match

---

📘 Best Practices

✅ Tip	💡 Why It Matters
Use WITH QUERY EXPANSION with relevance scoring	Boosts semantic matches
For CJK, always enable WITH PARSER ngram	Enables proper tokenization
Tune ngram_token_size for match length precision	1-2 for CJK, 3+ for Latin short codes
Avoid Boolean Mode with Query Expansion	They are incompatible
Rebuild FULLTEXT index after parser/config change	Required to apply new token size or parser

---

📌 Summary – Recap & Next Steps

MySQL’s Query Expansion and Ngram Parser extend Full-Text Search with intelligence and internationalization.

🔍 Key Takeaways

• Use WITH QUERY EXPANSION to auto-broaden queries
• Use WITH PARSER ngram for multilingual and partial matches
• Tune ngram_token_size as needed for your language/text length
• Query Expansion works only in Natural Language mode
• Combine both for smart + flexible search solutions

⚙️ Real-World Relevance

Essential in global search engines, multi-language blogs, news sites, e-commerce, and AI-powered knowledge bases.

---

❓ FAQ – MySQL Query Expansion & Ngram

---

❓ Can I use query expansion with Boolean mode?

❌ No. WITH QUERY EXPANSION is only compatible with natural language mode.

---

❓ What is the default Ngram token size?

✅ 2 for InnoDB. You can change it in my.cnf.

---

❓ Do I need a special parser for Chinese or Japanese?

✅ Yes. Use WITH PARSER ngram in your FULLTEXT index.

---

❓ How do I know if ngram indexing is working?

Use:

SHOW CREATE TABLE your_table;

✅ Look for WITH PARSER ngram in the FULLTEXT definition.

---

❓ Can I combine query expansion and ngram?

✅ Yes, but query expansion still only works in natural language mode.

---

Share Now :