This is really weird. I have a database with a field called "name". It has, among others, these values: The Blood of My People Karate Made E-Z Under the Ground People Can't Dance Under a Red Sky Blood Red John and the Well Listen People We're Over London etc If I do this: SELECT * FROM table WHERE MATCH name AGAINST ('blood' IN BOOLEAN MODE) I get this result: The Blood of My People Blood Red If I do this: SELECT * FROM table WHERE MATCH name AGAINST ('people' IN BOOLEAN MODE) I get this result: The Blood of My People People Can't Dance Listen People BUT if I do this: SELECT * FROM table WHERE MATCH name AGAINST ('under' IN BOOLEAN MODE) I get no returned rows. Same with "over'. This: SELECT * FROM table WHERE MATCH name AGAINST ('+under +ground' IN BOOLEAN MODE) gives me results for 'ground' but it ignores 'under', as if I hadn't typed it in. I am staring at this thing, and remain perplexed. No matter what terms I type in, I get results that make sense. By sheer chance, I discovered that these two aren't returning results that make any sense to me. Is there a list of special words I have to escape somehow, or... ???! I have a sneaking suspicion it's something really stupid and obvious, but I'm not seeing it, and I can't find it mentioned anywhere else. Any insights before I take my computer down to the exorcist? (BTW I am doing this in PHP.) ADDED: Just found out "behind" doesn't seem to work either, although "front" does. (One doesn't return the result "Behind the Front", one does.)
It sounds like there is a bug in the mysql MATCH function. All the words you are telling us fail are at the beginning of the title name. So try searching for "Under", "Blood" and see if they match. If they do, then you know its a bug that you have to report and work around. A workaround would be to lowercase the title and search on that. MATCH lower(name) AGAINST ('blood' IN BOOLEAN MODE). Or something like that.
After I read this, I created a phony name called "Front the Behind". Searching on the word 'front' worked in this case. ('behind' brought up nothing, as usual.) So, it seems to be the actual word, regardless of where it is. I couldn't get the lower thing to work (either SELECT lower(*) ... or SELECT * FROM table WHERE MATCH lower(name) ...). (It didn't like either syntax.) It does seem like a bug, but 1. Who would I report it to? What good would it do? 2. How come I can't find anything about this elsewhere on the internet? Seems like it woulda come up by now! Thanks.
Well I still don't get it, but I think this might work as a workaround: If a user does a search, and nothing comes up, (mysql_num_rows == 0), try the search again with wildcards. For example, searching for 'under' doesn't do anything. So search for '%under%'. I hate to increase the number of hits to the database, but hey. BTW the website is now in my signature, music-nerds.com. I dare you to search for albums that have 'under' in them
Over, under, and behind are "stop words". Stop words will not match within full-text queries. The same applies to words under four characters. To see the stop word list: http://dev.mysql.com/doc/refman/5.0/en/fulltext-stopwords.html To edit the default stop word list: http://dev.mysql.com/doc/refman/5.0/en/fulltext-fine-tuning.html