Mysql Fulltext Secret Bug … I mean feature.May 27, 2011 — Afnan Zari
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.
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)
1. I replaced db value to "_eight" from "eight" …
2. and before doing match against … i replaced "eight" with "_eight".