6.8.2 Fine-tuning MySQL Full-text Search

Unfortunately, full-text search has few user-tunable parameters yet, although adding some is very high on the TODO. If you have a MySQL source distribution (see section 2.3 Installing a MySQL Source Distribution), you can exert more control over full-text searching behaviour.

Note that full-text search was carefully tuned for the best searching effectiveness. Modifying the default behaviour will, in most cases, only make the search results worse. Do not alter the MySQL sources unless you know what you are doing!

The full-text variables described in the following list must be set at server startup time. You cannot modify them dynamically while the server is running.

• The minimum length of words to be indexed is defined by the MySQL variable ft_min_word_len. See section 4.5.7.4 SHOW VARIABLES. (This variable is only available from MySQL version 4.0.) The default value is four characters. Change it to the value you prefer, and rebuild your FULLTEXT indexes. For example, if you want three-character words to be searchable, you can set this variable by putting the following lines in an option file:

  [mysqld]
  ft_min_word_len=3
  

  Then restart the server and rebuild your FULLTEXT indexes.
• The stopword list can be loaded from the file specified by the ft_stopword_file variable. See section 4.5.7.4 SHOW VARIABLES. Rebuild your FULLTEXT indexes after modifying the stopword list. (This variable is only available from MySQL version 4.0.10 and onwards)
• The 50% threshold is determined by the particular weighting scheme chosen. To disable it, change the following line in `myisam/ftdefs.h':

  #define GWS_IN_USE GWS_PROB
  

  To:

  #define GWS_IN_USE GWS_FREQ
  

  Then recompile MySQL. There is no need to rebuild the indexes in this case. Note: by doing this you severely decrease MySQL's ability to provide adequate relevance values for the MATCH() function. If you really need to search for such common words, it would be better to search using IN BOOLEAN MODE instead, which does not observe the 50% threshold.
• Sometimes the search engine maintainer would like to change the operators used for boolean full-text searches. These are defined by the ft_boolean_syntax variable. See section 4.5.7.4 SHOW VARIABLES. Still, this variable is read-only; its value is set in `myisam/ft_static.c'.

For full-text changes that require you to rebuild your FULLTEXT indexes, the easiest way to do so for a MyISAM table is to use the following statement, which rebuilds the index file:

mysql> REPAIR TABLE tbl_name QUICK;

User Comments

#define GWS_IN_USE GWS_FREQ - I know this would be difficult to make a configurable option in my.cnf but it would be nice. GWS_PROB is not my cup of tea, unfortunately.

I have not found a reliable way to bypass the 50% threshold using boolean searches.

I just found this little nugget in the INSTALL-SOURCE
documentation...

* To use `MATCH ... AGAINST (... IN BOOLEAN
MODE)' with your tables, you need to rebuild them
with `ALTER TABLE table_name TYPE=MyISAM',
*even* if they are of `MyISAM' type.

Perhaps this should be in the fine tuning doc html...

Add your own comment.