MySQL: Setting up Full-Text Search Index
This article only applies to FogBugz 3, 4, and 5. FogBugz 6 and above no longer users FTS from the database. It is handled automatically by FogBugz.
This is not meant to be a comprehensive guide. That already exists here. This is a "quick start guide".
1. Do you have a full-text index set up?
Run this statement in MySQL:
SELECT DISTINCT Bug.ixBug as ixBug
FROM (Bug INNER JOIN BugEvent ON Bug.ixBug = BugEvent.ixBug)
WHERE MATCH(s) AGAINST('banana')
If it blows up, proceed to the next step. If it does not return an error (or even better, if you get rows returned), you should be all set! Do a search from FogBugz and it should succeed as well.
2. Create the index
Run this statement in MySQL:
CREATE FULLTEXT INDEX fulltextindex ON BugEvent (s)
If you get any errors here, that's outside the scope of this quick start guide, here is the comprehensive guide to full text search.
If you don't get errors, run Step 1 again.
(Once you create the index, MySQL will take care of populating it in the future.)
WARNING: MySQL defaults to a mimimum word length of 4 letters, for searches. See this article on the MySQL site for full details. Here's the relevant excerpt:
- The minimum and maximum length of words to be indexed is defined by the ft_min_word_len and ft_max_word_len system variables (available as of MySQL 4.0.0).
- The default minimum value is four characters. The default maximum depends on your version of MySQL.
- If you change either value, you must rebuild your FULLTEXT indexes (see below).
- For example, if you want three-character words to be searchable, you can set the ft_min_word_len variable by putting the following lines in an option file:
[mysqld]
ft_min_word_len=3
ft_max_word_len=15 - Then restart the server and rebuild your FULLTEXT indexes - run these queries on your database:
ALTER TABLE BugEvent DROP INDEX fulltextindex
CREATE FULLTEXT INDEX fulltextindex ON BugEvent (s)
(The drop statement assumes the index was named fogbugzindex to begin with.) - Also note particularly the remarks regarding myisamchk in the instructions following this list.
