CODEWITHSUNDEEP
mysqlfull-text-search
user131891
user131891

Reputation: 21

MySQL fulltext search isn't matching as expected

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

Upvotes: 1

Views: 484

Answers (3)

Yuval F
Yuval F

Reputation: 20621

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.

Upvotes: 0

Brent
Brent

Reputation: 23722

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.

Upvotes: 1

Michael Madsen
Michael Madsen

Reputation: 55009

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.

Upvotes: 4

Related Questions