Mysql Fulltext Secret Bug … I mean feature.

I was recently doing a full text search in which "eight" keyword was used, and it didn’t showed any single result. It was working for all other cases.
Finally after little google searches … i visited this document.

http://dev.mysql.com/tech-resources/articles/full-text-revealed.html#stopwords

It says ….

Suppose you want to search for a text that contains "you want to".

 SELECT ... FROM table1
WHERE MATCH(textcolumn) AGAINST ('"you want to"' IN BOOLEAN MODE)

This is awful, for two reasons:

  • "You" and "want" and "to" are all stopwords if you’re using the default stopword list (see the list of stopwords at the end of this article). In fact this particular query always fails because all words are stopwords.
  • Even if they weren’t stopwords, there is nothing in a key that tells you what the relative position of the words is within the text (see the index file key structure at the start of this article).

So MySQL can’t get the answer just by looking in the index. The method has to be: first try to filter out the candidate rows by looking for whatever words aren’t stopwords, then examine each candidate row in the data file and make sure the words are there in the correct order. This means exact phrase searching can be a little slower than ordinary searching. On the other hand, MySQL won’t find "Sam was tall" if you search for "Sam grew tall". (If "was" and "grew" are both stopwords, there’s another DBMS that would find "Sam was tall". We think that looks like a bug.)

And … "eight" is also in this stop word list …. (i guess … for no reason)

My Solution:

1. I replaced db value to "_eight" from "eight" …

2. and before doing match against … i replaced "eight" with "_eight".

Share your thoughts & feedback

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: