`~` (tilde) operator in Boolean Full-Text Search in MySQL is behaving opposite as stated in MySQL developer website

by

  • Unexpected MySQL Boolean Full-Text Search behavior where tilde ~ operator boosts relevance scores instead of demoting

  • Observed in MySQL 8.0.40 when using MATCH() AGAINST(... IN BOOLEAN MODE) with ~term

  • Contradicts official MySQL documentation stating ~ should decrease relevance

Root

  • Term demotion via ~ doesn’t function as pure subtraction in relevance

  • Relevance scores are composites of:

  • Term Frequency (TF)

  • Inverse Document Frequency (IDF)

  • Document length

  • ~ creates a negative term weight but positive IDF contribution

  • High IDF (rarity) of demoted term counteracts negative weight

  • Search term interactions amplify overall scores through floating-point precision

Why This Happens in Real

  • IDF dominates scoring for rare terms regardless of operator

  • Real-world datasets contain skewed term

  • Language-specific characteristics affect weighting (e.g., Danish compound words)

  • Multi-term queries create complex score interactions undocumented in

  • Normalization differences between document lengths distort operator

Real-World

  • Critical search results get incorrect ranking

  • Demotion filters inadvertently promote irrelevant

  • Users receive contradictory recommendations (e.g., jobs explicitly filtered becoming top results)

  • Business logic failures in recommendation/personalization

  • Erosion of trust in platform accuracy

Example or

Original query and results:

SELECT title, MATCH(title) AGAINST('pædagog' IN BOOLEAN MODE) AS score 

FROM jobs 

WHERE MATCH(title) AGAINST('pædagog