Thursday, May 21, 2009

mySQL FULLTEXT search returns no results: ft_min_word_len

MySQL FULLTEXT search query worked well until I tried to find "boy" in my DB.
No results, and I can see some records with this word directly on my phpMyAdmin screen.
The same repeated with the word "red". It can find, "blue"< "green" and "yellow", but not "red".

Well, after some searching I found that FULLTEXT in mySQL has two server variables (well, it have more of course, but these two are relevant for this issue):
ft_min_word_len = 4
ft_max_word_len = 84

Words less than 4 chars will not be indexed until you change ft_min_word_len to something less than 4 chars.

So, on Redhat/Fedora and many other Linuxes you have to edit /etc/my.cnf
The addition might look:

[mysqld]
ft_min_word_len=1
ft_max_word_len=32

Note [mysqld] header - if it is already in that file, just put two lines woth ft_ under it. It's important! If you put it under another header mySQL will not recognize it. Cost me about 2 hours two figure this stupid thing out since typically "[...]" headers are ignored by the parsing software (used as comments).
But obviously, not in my.cnf and mySQL.

On Windows with XAMPP there is a file called "my" (just "my", now extension whatsoever) in /mysql/bin folder.
It's the same my.cnf, so just add the lines into [mysqld] section there.

Verify that the change worked in phpMyAdmin ->localhost -> Varaibles

The last thing to do to get the things working is rebuilding all FULLTEXT indexes, if you got some data in the DB yet.
With phpMyAdmin you can just click "edit" link next to the FULLTEXT index name, and when the edit screen opens, click "Save" button. You will see that mySQL runs actually 'drop the old index and create the new index" query.