Phrase vs Distance Matching in MySQL Full-Text Search

🔤 Phrase Matching ("word1 word2")

This is used to search for exact phrases, with:

• Exact order

• No extra words in between

• No variations

Example:

MATCH(content) AGAINST('"gene therapy" IN BOOLEAN MODE) 

Matches:

• "gene therapy is promising"

Doesn’t match:

• "therapy for gene mutation"

• "gene-based cell therapy"

Phrase matching is strict — it's looking for gene immediately followed by therapy.

🧲 Proximity Matching ("word1 word2" @N)

This is used to search for all words appearing close together, regardless of order, and allows for flexibility in between.

Example:

MATCH(content) AGAINST('"gene therapy" @3' IN BOOLEAN MODE) 

Matches:

• "gene therapy is promising" ✅ (distance: 1)

• "therapy for gene mutation" ✅ (distance: 2 — therapy, for, gene)

• "a therapy based on gene editing" ✅ (distance: 3 — within threshold)

Doesn’t match:

• "gene expression has little relation to cell therapy" ❌ (too far apart)

So while "gene therapy" (phrase) only matches that exact sequence, "gene therapy" @3 allows them to be near each other in any order and with some wiggle room between them.

🆚 Summary Comparison

🤔 When to use which?

• Use phrase matching when you're looking for exact quotations or tight phrases (e.g., "climate change", "artificial intelligence").

• Use proximity search when you're more interested in conceptual relevance, especially in large or noisy text fields (e.g., medical records, article abstracts, etc.).
  

References:

• https://dev.mysql.com/doc/refman/8.4/en/fulltext-boolean.html

• ChatGPT 4o