MySQL FullText & MATCH() AGAINST()

Marius0188
Marius0188 used Ask the Experts™
on
Good day,

I am using MySQL FullText indexing on a new project of mine.
What I don't understand is how inconsistent this functionality is.

For example:
I have the following two rows in a table of 1 column >
Column1
--------------
Value1
Value2


When searching for "Value1" (exact unique match) then it returns this row.
But when searching for "Value2" (exact unique match) then it WONT return anything.

In my real database I have the following unique values representing Value1 & Value2 in my example:
1) "YFY678GP"
2) "XT 660 R"

The only difference being value 2 contains spaces.
Could this be so big issue?

Please explain the  logic of FullText indexing in conjunction with MATCH AGAINST?

Thanks to ALL :)
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2012
Commented:
By default fulltext will not search for keywords less than 3 characters in length.  These words will be ignored from your search parameters.  To change the minimum search keyword length you need to change the value of ft_min_word_len server variable, restart mysql, then rebuild your full-text indexes.  See also http://dev.mysql.com/doc/refman/5.5/en/fulltext-fine-tuning.html for other things you need to consider (stop words, etc)

Author

Commented:
Mmmh I see.
Less than 3 characters you say.

1. But "XT 660 R" wont it at least match the "660" phrase?
2. I have shared hosting account - do you know where I can set these mysql server settings?

Thanks a lot!
:)
Top Expert 2012

Commented:
1.  Can you give me your actual statement calling the fulltext function?
2.  Unfortunately you can't on a shared server.  And even if you didn't need to change the settings I don't recommend running an application that regularly uses fulltext on it - it will be slow and/or your host will tell you your app is consuming too much resources.
Top Expert 2012

Commented:
Update on this: by default I don't think fulltext searches for numbers.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial