MySQL: Improving MySQL Fulltext Search
The default settings for MySQL full-text search are not optimal for many FogBugz users. Specifically, the default minimum word length is too long at four characters, and the stopwords list contains too many words that may be of interest, particularly in phrase searches. On MySQL versions 4.0 and up, you can adjust these settings.
MySQL provides a guide to tuning Full-Text Search that covers this in much more detail, but here is a quick start to getting better search results.
Create your own stopwords file
In a directory accessible by MySQL (e.g. mysql/data) create a custom stopwords file containing words that are not relevant in searching, or a blank file for no stopwords. I recommend starting out with a blank stopwords file.
Update the MySQL configuration file
Find your my.cnf or my.ini file and add these values:
(under mysqld)
ft_min_word_len=3
ft_max_word_len=15
ft_stopword_file = [path to your stopword file]
(under myisamchk)
ft_min_word_len=3
ft_max_word_len=15
Restart MySQL
Now restart MySQL. This is important, because you need to pick up the new configuration
Update the full-text index on the BugEvent table
Connect to your FogBugz database in MySQL and run the following command.
mysql> REPAIR TABLE BugEvent QUICK;
