MySql search has no way to describe a string as literal (as a word) – it stumbles on periods

(written by lawrence krubner, however indented passages are often quotes). You can contact lawrence at: lawrence@krubner.com, or follow me on Twitter.

This week I’ve been battling against MySql’s limits. Apparently its builtin search (MATCH/AGAINST) can not handle acronyms divided by periods. MySql fails badly here:

SELECT * from chocolate_log WHERE ( MATCH( BRAND_PLUS ) AGAINST (+”C.A.O” ) ) ORDER BY score desc, TASTING_DATE DESC, SORT_BY ASC;

There is no way to get MySql to see the C.A.O. There are no quotes that allow this to become a literal, and there is no special notation that would allow me to mark this as a word. If I use LIKE, I get back 266 rows out of a table with 13,000 rows, but MATCH AGAINST gets me zero.

MySql sees the periods as stopwords, so MySql things the above is a search for the 3 words C and A and O. But its minimum search word is 4 letters long, so single words do not get search for. So I get zero results. I could change the minimum word length to 1, but then we would get tons of irrelevant results on any of our other searches.

MySql search is very limited.

More info at StackOverflow.

Source