pata — 2011-01-27T12:02:37-05:00 — #1
Anyone have a clue why FULLTEXT search would fail on a term like "ac/dc"?
select id, title from table where match(title) against("ac/dc" in boolean mode)
I am aware the words with less than 4 characters are ignored, but this should work no?
r937 — 2011-01-27T12:57:35-05:00 — #2
was there an error message?
pata — 2011-01-27T13:47:48-05:00 — #3
I meant it returns no rows, doesn't find anything.
r937 — 2011-01-27T14:16:19-05:00 — #4
how many total rows in the table? and how many contain this string when you use LIKE to find them?
pata — 2011-01-27T14:17:21-05:00 — #5
total rows about 8, 2 rows contain ac/dc.
Sorry, not in front of the other computer right now.
r937 — 2011-01-27T14:20:40-05:00 — #6
from da manual...
[fulltext searching] works best with large collections (in fact, it was carefully tuned this way). For very small tables, word distribution does not adequately reflect their semantic value, and this model may sometimes produce bizarre results.
pata — 2011-01-27T15:19:04-05:00 — #7
so I guess in proper English, this translates to something like "my table doesn't have enough rows". I now have 21 entries with the same result. I doubt that constitutes a "very small table".
Furthermore, other fulltext searches work fine (searches for "school" or "school of rock", etc), just not with ac/dc so I doubt that this is what's causing it. Of course I could be wrong.
guelphdad — 2011-01-27T15:56:26-05:00 — #8
yes that would still constitute a very small table.
I believe the problem is the / which MAY be interpreted as you now searching on two two letter words "ac" and "dc" and those would be stop words because the minimum word length is four characters. I believe you can change that setting. I do think though this is why you run into the trouble.
Change a couple of rows to acdc without the / and search on that and I'll bet the rows are returned.
pata — 2011-01-27T16:01:53-05:00 — #9
yes, this was my same conclusion, but then how do I deal with terms that have a slash in them?
Because the same search using LIKE does work.
doug_g — 2011-01-27T17:49:11-05:00 — #10
Did you try using single quotes around 'ac/dc' ?
pata — 2011-01-28T01:33:47-05:00 — #11
Yes I did. Single, double, same thing. It's really got me stumped!
pata — 2011-01-28T02:12:17-05:00 — #12
I think what is happening is that FULLTEXT is dividing the term into 2 words because of the slash and of course it will ignore words of 3 characters or less. The question is, is there a way to tell it not to divide words when there is a slash present, it does the same thing for a hyphen.
pata — 2011-03-11T08:42:45-05:00 — #13
Anyone have any clues as to why this is not working for fulltext search and how to circumvent it?
Any help would be appreciated.