4

I make a PHP MYSQL FULLTEXT search, but how to make a Precise search? As I use some following code, I need return the data which must contain word 'new' and 'york', em... in fact the code will return some data only contain 'new' but never has 'york'. So how? 

SELECT * FROM text WHERE MATCH (title,body) AGAINST('new+york' IN BOOLEAN MODE)
Improve this question

2 Answers 2

Reset to default
2

You've stumbled upon an edge case -- the problem isn't what you think it is.

MySQL fulltext indexing by default will not index words under four characters in length, like "new". If you want to search for "new", then you'll need to adjust the minimum word length and rebuild your indexes.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

1

Although Charles is correct regarding the length of the word 'new', there is another issue here - even reindexing with a shorter minimum word length (not entirely advisable), you will get content that contains the word 'new' and 'york' and many words in between them, but not necessarily just the string 'new york'.

Also, there's a good chance that the word 'new' is in more then 50% of your records, so no records will return for that.

I came up with a way of incorporating exact string matching with fulltext indexes a couple of years ago - though I never got around to including boolean mode searches as well.

Improve this answer

3 Comments

The "oh, this word is everywhere, let's forget it" behavior has always bugged me, good call.
@Charles Yeah, it made testing on limited content rather difficult when I first started out
Ok, I have read the articles and know how to solve, thanks @HorusKol, and for my careless, it should be '+new +york'.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.