0

I have a query on fulltext that seems to work with a misspellings if I'm using 2 words but it doesn't if I'm using just one word

I have a table with fullname field

And in the table "Nile Rodgers","Bam Rogers","Catherine Wyn Rogers","Nigel Rogers"

my query

SELECT *
FROM people
WHERE MATCH (fullname) AGAINST ('myquerytext' IN NATURAL LANGUAGE MODE)

if I search for "Rodgers" I found "Nile Rodgers"

if I search for "Rogers" I found "Bam Rogers","Catherine Wyn Rogers","Nigel Rogers" BUT NOT "Nile Rodgers"

if I search "Nile Rogers" I fond "Nile Rodgers","Bam Rogers","Catherine Wyn Rogers","Nigel Rogers"

Is it possible to adjust the tolerance?

al404IT
  • 1,380
  • 3
  • 21
  • 54
  • 1
    My guess would be that `'Nile Rogers'` returns all the records, not because MySQL is handling alternate spellings, but because all the records match one of those 2 tokens. See https://stackoverflow.com/questions/7200935/best-way-to-deal-with-misspellings-in-a-mysql-fulltext-search. If you want to handle alternate spellings/misspellings in MySQL, you can do something like create a SOUNDEX index. If switching RDBMSs is an option, Postgres has built-in support for word n-grams, which you can use to compare a word to its misspelled equivalent. – Zack Sep 12 '18 at 15:54

1 Answers1

0

Your issue is that it searches EITHER Nile OR Rogers, and so the results you get are logical.

If you want ALL the words and not ANY of them, use BOOLEAN MODE and add a + in front of each word :

SELECT *
FROM people
WHERE MATCH (fullname) AGAINST ('+Nile +Rogers' IN BOOLEAN MODE)

12.9.2 Boolean Full-Text Searches

In implementing this feature, MySQL uses what is sometimes referred to as implied Boolean logic, in which

• + stands for AND

• - stands for NOT

• [no operator] implies OR

Offcial doc

Thomas G
  • 9,886
  • 7
  • 28
  • 41