MySQL fulltext search isn't matching as expected.

Question

I have a pretty simple query that doesn't seem to be giving me the results I'd like. I'm trying to allow the user to search for a resturant by its name, address, or city using a fulltext search.

here is my query:

SELECT ESTAB_NAME, ESTAB_ADDRESS, ESTAB_CITY
FROM restaurant_restaurants rr
WHERE 
  MATCH (rr.ESTAB_NAME, rr.ESTAB_ADDRESS, rr.ESTAB_CITY) 
  AGAINST ('*new* *hua*' IN BOOLEAN MODE) 
LIMIT 0, 500

New Hua is the restaurant that exists within the table. However when i do a search for 'ting ho' i get the results I would expect.

Does anyone have any idea what What is going on? I'm using a MyISAM storage engine on MySQL version 5.0.41

Answer 1

Most likely, the full-text index settings have set a minimum word length of 4 - I believe this is the default. You'll need to change these settings, even for BOOLEAN MODE (as per http://dev.mysql.com/doc/refman/5.1/en/fulltext-boolean.html). Take a look at http://dev.mysql.com/doc/refman/5.1/en/fulltext-fine-tuning.html for the settings to change.

Answer 2

I think Michael is right, but also, you probably want to remove the * characters unless that's actually in the title you're searching for. MATCH AGAINST doesn't require a "match all" type of parameter.

Answer 3

My guess: "new" is a Mysql Default Stop Word. See Michael Madsen's second link to see how to change the stop word list and regain the restaurant.