(mysql.info) fulltext-fine-tuning

Info Catalog

(mysql.info) fulltext-restrictions

(mysql.info) fulltext-search

 
 12.7.5 Fine-Tuning MySQL Full-Text Search
 -----------------------------------------
 
 MySQL's full-text search capability has few user-tunable parameters.
 You can exert more control over full-text searching behavior if you
 have a MySQL source distribution because some changes require source
 code modifications. See  installing-source.
 
 Note that full-text search is carefully tuned for the most
 effectiveness. Modifying the default behavior in most cases can
 actually decrease effectiveness. _Do not alter the MySQL sources unless
 you know what you are doing_.
 
 Most full-text variables described in this section must be set at
 server startup time. A server restart is required to change them; they
 cannot be modified while the server is running.
 
 Some variable changes require that you rebuild the `FULLTEXT' indexes
 in your tables. Instructions for doing this are given at the end of
 this section.
 
    * The minimum and maximum lengths of words to be indexed are defined
      by the `ft_min_word_len' and `ft_max_word_len' system variables.
      (See  server-system-variables.) The default minimum value
      is four characters; the default maximum is version dependent. If
      you change either value, you must rebuild your `FULLTEXT' indexes.
      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
 
      Then you must restart the server and rebuild your `FULLTEXT'
      indexes. Note particularly the remarks regarding `myisamchk' in the
      instructions following this list.
 
    * To override the default stopword list, set the `ft_stopword_file'
      system variable. (See  server-system-variables.) The
      variable value should be the pathname of the file containing the
      stopword list, or the empty string to disable stopword filtering.
      After changing the value of this variable or the contents of the
      stopword file, restart the server and rebuild your `FULLTEXT'
      indexes.
 
      The stopword list is free-form. That is, you may use any
      non-alphanumeric character such as newline, space, or comma to
      separate stopwords. Exceptions are the underscore character (`_')
      and a single apostrophe (`'') which are treated as part of a word.
      The character set of the stopword list is the server's default
      character set; see  charset-server.
 
    * The 50% threshold for natural language searches is determined by
      the particular weighting scheme chosen. To disable it, look for
      the following line in `myisam/ftdefs.h':
 
           #define GWS_IN_USE GWS_PROB
 
      Change that line to this:
 
           #define GWS_IN_USE GWS_FREQ
 
      Then recompile MySQL. There is no need to rebuild the indexes in
      this case. * By making this change, 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.
 
    * To change the operators used for boolean full-text searches, set
      the `ft_boolean_syntax' system variable. This variable can be
      changed while the server is running, but you must have the `SUPER'
      privilege to do so. No rebuilding of indexes is necessary in this
      case. See  server-system-variables, which describes the
      rules governing how to set this variable.
 
 If you modify full-text variables that affect indexing
 (`ft_min_word_len', `ft_max_word_len', or `ft_stopword_file'), or if
 you change the stopword file itself, you must rebuild your `FULLTEXT'
 indexes after making the changes and restarting the server. To rebuild
 the indexes in this case, it is sufficient to do a `QUICK' repair
 operation:
 
      mysql> REPAIR TABLE TBL_NAME QUICK;
 
 Note that if you use `myisamchk' to perform an operation that modifies
 table indexes (such as repair or analyze), the `FULLTEXT' indexes are
 rebuilt using the _default_ full-text parameter values for minimum word
 length, maximum word length, and stopword file unless you specify
 otherwise. This can result in queries failing.
 
 The problem occurs because these parameters are known only by the
 server. They are not stored in `MyISAM' index files. To avoid the
 problem if you have modified the minimum or maximum word length or
 stopword file values used by the server, specify the same
 `ft_min_word_len', `ft_max_word_len', and `ft_stopword_file' values to
 `myisamchk' that you use for `mysqld'. For example, if you have set the
 minimum word length to 3, you can repair a table with `myisamchk' like
 this:
 
      shell> myisamchk --recover --ft_min_word_len=3 TBL_NAME.MYI
 
 To ensure that `myisamchk' and the server use the same values for
 full-text parameters, place each one in both the `[mysqld]' and
 `[myisamchk]' sections of an option file:
 
      [mysqld]
      ft_min_word_len=3
 
      [myisamchk]
      ft_min_word_len=3
 
 An alternative to using `myisamchk' is to use the `REPAIR TABLE',
 `ANALYZE TABLE', `OPTIMIZE TABLE', or `ALTER TABLE' statements. These
 statements are performed by the server, which knows the proper full-text
 parameter values to use.
 

Info Catalog

(mysql.info) fulltext-restrictions

(mysql.info) fulltext-search