DOC HOME SITE MAP MAN PAGES GNU INFO SEARCH PRINT BOOK
 

(mysql.info.gz) Fulltext Fine-tuning

Info Catalog (mysql.info.gz) Fulltext Restrictions (mysql.info.gz) Fulltext Search (mysql.info.gz) Fulltext TODO
 
 12.6.4 Fine-Tuning MySQL Full-Text Search
 -----------------------------------------
 
 The MySQL full-text search capability has few user-tunable parameters
 yet, although adding more is very high on the TODO.  You can exert more
 control over full-text searching behavior if you have a MySQL source
 distribution because some changes require source code modfications.
  Installing source.
 
 Note that full-text search was carefully tuned for the best searching
 effectiveness.  Modifying the default behavior will, in most cases,
 make the search results worse.  Do not alter the MySQL sources unless
 you know what you are doing!
 
 Most full-text variables described in the following items must be set at
 server startup time. For these variables, a server restart is required
 to change them and you cannot modify them dynamically 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 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).   Server system variables.
      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.  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 restart the server and rebuild your `FULLTEXT' indexes.  Also
      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 (available as of MySQL 4.0.10).   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, rebuild your
      `FULLTEXT' indexes.
 
    * 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 the line to this:
 
           #define GWS_IN_USE GWS_FREQ
 
      Then recompile MySQL.  There is no need to rebuild the indexes in
      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 (available as of MySQL
      4.0.1).  The variable also can be changed while the server is
      running, but you must have the `SUPER' privilege to do so.  No
      index rebuilding is necessary.   Server system variables
      describes the rules that define 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'), you must
 rebuild your `FULLTEXT' indexes after making the changes and restarting
 the server. To rebuild the indexes in this case, it's sufficient to do
 a `QUICK' repair operation:
 
      mysql> REPAIR TABLE TBL_NAME QUICK;
 
 With regard specifically to using the `IN BOOLEAN MODE' capability, if
 you upgrade from MySQL 3.23 to 4.0 or later, it's necessary to replace
 the index header as well.  To do this, do a `USE_FRM' repair operation:
 
      mysql> REPAIR TABLE TBL_NAME USE_FRM;
 
 This is necessary because boolean full-text searches require a flag in
 the index header that was not present in MySQL 3.23, and that is not
 added if you do only a `QUICK' repair.  If you attempt a boolean
 full-text search without rebuilding the indexes this way, the search
 will return incorrect results.
 
 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 and
 maximum word length and the 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 the
 stopword file in 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, you can 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'.  These statements
 are performed by the server, which knows the proper full-text parameter
 values to use.
 
Info Catalog (mysql.info.gz) Fulltext Restrictions (mysql.info.gz) Fulltext Search (mysql.info.gz) Fulltext TODO
automatically generated byinfo2html