Apa itu full text search mysql

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 Section 2.9, “Installing MySQL from Source”.

Full-text search is carefully tuned for 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 so are given later in this section.

Configuring Minimum and Maximum Word Length

The minimum and maximum lengths of words to be indexed are defined by the and for

#define GWS_IN_USE GWS_PROB

#define GWS_IN_USE GWS_PROB

Minimum and maximum word length full-text parameters do not apply to FULLTEXT indexes created using the ngram parser. ngram token size is defined by the option.

After changing any of these options, rebuild your FULLTEXT indexes for the change to take effect. For example, to make two-character words searchable, you could put the following lines in an option file:

[mysqld]
innodb_ft_min_token_size=2
ft_min_word_len=2

Then restart the server and rebuild your FULLTEXT indexes. For

#define GWS_IN_USE GWS_PROB

#define GWS_IN_USE GWS_PROB

Configuring the Natural Language Search Threshold

For

#define GWS_IN_USE GWS_PROB

#define GWS_IN_USE GWS_FREQ

#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 function. If you really need to search for such common words, it would be better to search using

#define GWS_IN_USE GWS_FREQ

Modifying Boolean Full-Text Search Operators

To change the operators used for boolean full-text searches on

#define GWS_IN_USE GWS_PROB

#define GWS_IN_USE GWS_PROB

Character Set Modifications

For the built-in full-text parser, you can change the set of characters that are considered word characters in several ways, as described in the following list. After making the modification, rebuild the indexes for each table that contains any FULLTEXT indexes. Suppose that you want to treat the hyphen character ('-') as a word character. Use one of these methods:

• Modify the MySQL source: In

  #define GWS_IN_USE GWS_FREQ
  

  8 (for

  #define GWS_IN_USE GWS_PROB
  

  0), or in

  #define GWS_IN_USE GWS_FREQ
  

  1 (for

  #define GWS_IN_USE GWS_PROB
  

  3), see the

  mysql> set GLOBAL innodb_optimize_fulltext_only=ON;
  Query OK, 0 rows affected (0.01 sec)
  
  mysql> OPTIMIZE TABLE opening_lines;
  +--------------------+----------+----------+----------+
  | Table              | Op       | Msg_type | Msg_text |
  +--------------------+----------+----------+----------+
  | test.opening_lines | optimize | status   | OK       |
  +--------------------+----------+----------+----------+
  1 row in set (0.01 sec)    

  2 and

  mysql> set GLOBAL innodb_optimize_fulltext_only=ON;
  Query OK, 0 rows affected (0.01 sec)
  
  mysql> OPTIMIZE TABLE opening_lines;
  +--------------------+----------+----------+----------+
  | Table              | Op       | Msg_type | Msg_text |
  +--------------------+----------+----------+----------+
  | test.opening_lines | optimize | status   | OK       |
  +--------------------+----------+----------+----------+
  1 row in set (0.01 sec)    

  3 macros. Add

  mysql> set GLOBAL innodb_optimize_fulltext_only=ON;
  Query OK, 0 rows affected (0.01 sec)
  
  mysql> OPTIMIZE TABLE opening_lines;
  +--------------------+----------+----------+----------+
  | Table              | Op       | Msg_type | Msg_text |
  +--------------------+----------+----------+----------+
  | test.opening_lines | optimize | status   | OK       |
  +--------------------+----------+----------+----------+
  1 row in set (0.01 sec)    

  4 to one of those macros and recompile MySQL.

• Modify a character set file: This requires no recompilation. The

  mysql> set GLOBAL innodb_optimize_fulltext_only=ON;
  Query OK, 0 rows affected (0.01 sec)
  
  mysql> OPTIMIZE TABLE opening_lines;
  +--------------------+----------+----------+----------+
  | Table              | Op       | Msg_type | Msg_text |
  +--------------------+----------+----------+----------+
  | test.opening_lines | optimize | status   | OK       |
  +--------------------+----------+----------+----------+
  1 row in set (0.01 sec)    

  2 macro uses a “character type” table to distinguish letters and numbers from other characters. . You can edit the contents of the

  mysql> set GLOBAL innodb_optimize_fulltext_only=ON;
  Query OK, 0 rows affected (0.01 sec)
  
  mysql> OPTIMIZE TABLE opening_lines;
  +--------------------+----------+----------+----------+
  | Table              | Op       | Msg_type | Msg_text |
  +--------------------+----------+----------+----------+
  | test.opening_lines | optimize | status   | OK       |
  +--------------------+----------+----------+----------+
  1 row in set (0.01 sec)    

  6 array in one of the character set XML files to specify that

  mysql> set GLOBAL innodb_optimize_fulltext_only=ON;
  Query OK, 0 rows affected (0.01 sec)
  
  mysql> OPTIMIZE TABLE opening_lines;
  +--------------------+----------+----------+----------+
  | Table              | Op       | Msg_type | Msg_text |
  +--------------------+----------+----------+----------+
  | test.opening_lines | optimize | status   | OK       |
  +--------------------+----------+----------+----------+
  1 row in set (0.01 sec)    

  4 is a “letter.” Then use the given character set for your FULLTEXT indexes. For information about the

  mysql> set GLOBAL innodb_optimize_fulltext_only=ON;
  Query OK, 0 rows affected (0.01 sec)
  
  mysql> OPTIMIZE TABLE opening_lines;
  +--------------------+----------+----------+----------+
  | Table              | Op       | Msg_type | Msg_text |
  +--------------------+----------+----------+----------+
  | test.opening_lines | optimize | status   | OK       |
  +--------------------+----------+----------+----------+
  1 row in set (0.01 sec)    

  6 array format, see Section 10.13.1, “Character Definition Arrays”.

• Add a new collation for the character set used by the indexed columns, and alter the columns to use that collation. For general information about adding collations, see Section 10.14, “Adding a Collation to a Character Set”. For an example specific to full-text indexing, see Section 12.10.7, “Adding a User-Defined Collation for Full-Text Indexing”.

Optimizing InnoDB Full-Text Indexes

Running

mysql> REPAIR TABLE tbl_name QUICK;

To optimize a full-text index, enable and run

mysql> REPAIR TABLE tbl_name QUICK;

mysql> set GLOBAL innodb_optimize_fulltext_only=ON;
Query OK, 0 rows affected (0.01 sec)

mysql> OPTIMIZE TABLE opening_lines;
+--------------------+----------+----------+----------+
| Table              | Op       | Msg_type | Msg_text |
+--------------------+----------+----------+----------+
| test.opening_lines | optimize | status   | OK       |
+--------------------+----------+----------+----------+
1 row in set (0.01 sec)    

To avoid lengthy rebuild times for full-text indexes on large tables, you can use the option to perform the optimization in stages. The

mysql> REPAIR TABLE tbl_name QUICK;

mysql> REPAIR TABLE tbl_name QUICK;

mysql> REPAIR TABLE tbl_name QUICK;

mysql> REPAIR TABLE tbl_name QUICK;

mysql> REPAIR TABLE tbl_name QUICK;

Rebuilding MyISAM Full-Text Indexes

If you modify full-text variables that affect indexing (, , or ), 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 FULLTEXT indexes for a

#define GWS_IN_USE GWS_PROB

myisamchk --recover --ft_min_word_len=3 tbl_name.MYI

mysql> REPAIR TABLE tbl_name QUICK;

Alternatively, use

myisamchk --recover --ft_min_word_len=3 tbl_name.MYI

Each table that contains any FULLTEXT index must be repaired as just shown. Otherwise, queries for the table may yield incorrect results, and modifications to the table cause the server to see the table as corrupt and in need of repair.

If you use myisamchk to perform an operation that modifies

#define GWS_IN_USE GWS_PROB

The problem occurs because these parameters are known only by the server. They are not stored in

#define GWS_IN_USE GWS_PROB

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]
ft_min_word_len=3

[myisamchk]
ft_min_word_len=3

[mysqld]
ft_min_word_len=3

[myisamchk]
ft_min_word_len=3

[mysqld]
ft_min_word_len=3

[myisamchk]
ft_min_word_len=3

An alternative to using myisamchk for

#define GWS_IN_USE GWS_PROB

[mysqld]
ft_min_word_len=3

[myisamchk]
ft_min_word_len=3

[mysqld]
ft_min_word_len=3

[myisamchk]
ft_min_word_len=3

mysql> REPAIR TABLE tbl_name QUICK;

myisamchk --recover --ft_min_word_len=3 tbl_name.MYI