12.9.4. Full-Text Stopwords

The stopword list is loaded and searched for full-text queries using the server character set and collation (the values of the character_set_server and collation_server system variables). False hits or misses might occur for stopword lookups if the stopword file or columns used for full-text indexing or searches have a character set or collation different from character_set_server or collation_server.

Case sensitivity of stopword lookups depends on the server collation. For example, lookups are case insensitive if the collation is latin1_swedish_ci, whereas lookups are case sensitive if the collation is latin1_general_cs or latin1_bin.

Stopwords for InnoDB Search Indexes

InnoDB has a relatively short list of default stopwords, because documents from technical, literary, and so on sources often use short words as keywords or in significant phrases. For example, you might search for "to be or not to be" and expect to get a sensible result, rather than having all those words ignored.

To see the list, query the table information_schema.innodb_ft_default_stopword. To define your own stopword list used for all InnoDB tables, define a table with the same structure as innodb_ft_default_stopword, fill it with the desired stopwords, and set the value of the innodb_ft_server_stopword_table option to a value of the form db_name/table_name before creating the search index. To create special stopword lists on a table-by-table basis, define other tables to hold these lists and specify the appropriate one in the innodb_ft_user_stopword_table option before creating the search index.

Stopwords for MyISAM Search Indexes

In MySQL 5.6, the stopword file is loaded and searched using latin1 if character_set_server is ucs2, utf16, utf16le, or utf32. If any table was created with FULLTEXT indexes while the server character set was ucs2, utf16, utf16le, or utf32, it should be repaired using this statement:

REPAIR TABLE tbl_name QUICK;

The following table shows the default list of stopwords for MyISAM search indexes. In a MySQL source distribution, you can find this list in the storage/myisam/ft_static.c file.