Description:
Hello
I found out that MySQL FULL text search in Boolean Mode, does not count "new" as a word. I simply can't seem to get a result if my search term is "new". I tried this query for a database of properties in New York/ New Jersey
SELECT property_address,
MATCH (property_address)
AGAINST ('new*' IN BOOLEAN MODE)
as score
FROM properties_master WHERE
MATCH (property_address)
AGAINST ('new*'
IN BOOLEAN MODE)
ORDER BY score DESC
There were absolutely no results. Whereas atleast 300 properties have the word New York in their address field. Then I tried something slightly different to prove my point.
SELECT property_address,
MATCH (property_address)
AGAINST ('new* york*' IN BOOLEAN MODE)
as score
FROM properties_master WHERE
MATCH (property_address)
AGAINST ('new* york*'
IN BOOLEAN MODE)
ORDER BY score DESC
This time I got results. But when I checked the score, each record had a score of just 1. The boolean search clocked only York as the matching search term.
I wonder if there are any other such words that don't get searched? Has anyone noticed this? It seems "new" may be a reserved word! Any ideas how to get around this?
Thanks
Naresh
How to repeat:
I tried this query for a database of properties in New York/ New Jersey
SELECT property_address,
MATCH (property_address)
AGAINST ('new*' IN BOOLEAN MODE)
as score
FROM properties_master WHERE
MATCH (property_address)
AGAINST ('new*'
IN BOOLEAN MODE)
ORDER BY score DESC
There were absolutely no results. Whereas atleast 300 properties have the word New York in their address field. Then I tried something slightly different to prove my point.
SELECT property_address,
MATCH (property_address)
AGAINST ('new* york*' IN BOOLEAN MODE)
as score
FROM properties_master WHERE
MATCH (property_address)
AGAINST ('new* york*'
IN BOOLEAN MODE)
ORDER BY score DESC
This time I got results. But when I checked the score, each record had a score of just 1. The boolean search clocked only York as the matching search term.
Suggested fix:
There doesn't seem to be a documented work-around to this
Description: Hello I found out that MySQL FULL text search in Boolean Mode, does not count "new" as a word. I simply can't seem to get a result if my search term is "new". I tried this query for a database of properties in New York/ New Jersey SELECT property_address, MATCH (property_address) AGAINST ('new*' IN BOOLEAN MODE) as score FROM properties_master WHERE MATCH (property_address) AGAINST ('new*' IN BOOLEAN MODE) ORDER BY score DESC There were absolutely no results. Whereas atleast 300 properties have the word New York in their address field. Then I tried something slightly different to prove my point. SELECT property_address, MATCH (property_address) AGAINST ('new* york*' IN BOOLEAN MODE) as score FROM properties_master WHERE MATCH (property_address) AGAINST ('new* york*' IN BOOLEAN MODE) ORDER BY score DESC This time I got results. But when I checked the score, each record had a score of just 1. The boolean search clocked only York as the matching search term. I wonder if there are any other such words that don't get searched? Has anyone noticed this? It seems "new" may be a reserved word! Any ideas how to get around this? Thanks Naresh How to repeat: I tried this query for a database of properties in New York/ New Jersey SELECT property_address, MATCH (property_address) AGAINST ('new*' IN BOOLEAN MODE) as score FROM properties_master WHERE MATCH (property_address) AGAINST ('new*' IN BOOLEAN MODE) ORDER BY score DESC There were absolutely no results. Whereas atleast 300 properties have the word New York in their address field. Then I tried something slightly different to prove my point. SELECT property_address, MATCH (property_address) AGAINST ('new* york*' IN BOOLEAN MODE) as score FROM properties_master WHERE MATCH (property_address) AGAINST ('new* york*' IN BOOLEAN MODE) ORDER BY score DESC This time I got results. But when I checked the score, each record had a score of just 1. The boolean search clocked only York as the matching search term. Suggested fix: There doesn't seem to be a documented work-around to this