Can someone explain why ear ache in quotes allows rows to be returned that contain stomach ache? Shouldn’t the use of quotations return an exact match?
MATCH (keyword) AGAINST (‘“ear ache"’ in boolean mode)
Thanks!
in boolean mode, the words inside quotes are searched for individually
this is explained in the manual
ear is only 3 letters, so it’s treated as a stopword
That’s why long ago I decided that mysq fulltext search is useless - stopwords which are ignored, if too many matches are found then none are returned, almost no customization, etc. I don’t know who would like to use it? I suppose mysql.com uses fulltext search in their search engine - that would explain that I hardly ever can find anything using their search box in the documentation, now I don’t even bother and use google to search mysql.com.
In my sites I usually use a separate table where all data for searching are indexed and prepared in a proper form (eg. stripped tags, punctuation marks, accents on letters, etc.) and then I write code in my application that will convert the phrase to search into a series of LIKE and/or REGEXP expressions joined with AND or OR and this will give me enough room for customising how the search needs to function. Alas, I don’t get the benefit of the speedy index but with a bit of caching mechanisms I can cope all right.
I haven’t tried it myself (yet), but you could try installing Spinx and search using that instead of with MySQL, which as others have pointed out isn’t very good at searching. You can find a nice tutorial on Sphinx over here: http://www.ibm.com/developerworks/library/os-php-sphinxsearch/
If it’s your mysql server you can change settings to allow 3 character words to work in fulltext. I did this on my internal mediawiki wiki and it definitely makes search more useful. I suspect doing this change on a heavily loaded busy server wouldn’t be wise, though.
DB-based fulltext search is limited and you typically need to do a bit more thinking than just calling the default fulltext functions.
These days I would run with something lucene based if I needed search to work right. SOLR is a great option here – it is lucene as a service with a HTTP API.
I used a shared server at my local ISP. Should I just ask them to make the change? Or would it be a waste of my time because they likely won’t do it?
It is doubtful they would make the change. You could ask.