MySQL MATCH AGAINST not find results if an underscore is attached to the search term

Question

I use full text indexing to find results faster and it works well except when the term i search for is attached to an underscore inside the database record.

---

My database records:

article.title
++++++++++++++++++++++++++++++

My article 123456 created
------------------------------
My article new_123456 created
------------------------------
My article 123456_new created

---

My match against query:

MATCH(article.title) AGAINST ( "123456*" IN BOOLEAN MODE )

---

This query return only the first record and ignore the others since the term "123456" is attached to an underscore ( _ ), either before or after the term, the query will ignore the records.

What is the thing I did wrong and how to fix this problem?

Answer 1

There are many things that can mess up FULLTEXT:

• Punctuation
• "stop words"
• min word "length"
• Language

It is sometimes best to edit the data before storing it. In your case, replacing "_" with " " might be the 'right' solution. That could be done either in your application code as you insert strings, or by using MySQL's REPLACE() as the string is INSERTed.