How to Configure Full-Text Stopwords & Stoplist in MySQL

Mysql stopwords is a list of most commonly used English language words like some, about, by, from, by… and much more. These stop words do not match if they are present in the search string.
These stopwords are ignored in full-text search because these words are more likely to present in almost every string and it’s not possible to calculate relevancy in this case. So MySQL ignores those words to reduce result pollution and for efficiency.

NOTE- 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

To see the default InnoDB stopword list, query the INFORMATION_SCHEMA. INNODB_FT_DEFAULT_STOPWORD table.

mysql > SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_DEFAULT_STOPWORD;

You also can define your own stopword list or you can alter the stopword list for all InnoDB tables, define a table with the same structure as the INNODB_FT_DEFAULT_STOPWORD table, populate it with stopwords, and set the value of the innodb_ft_server_stopword_table option to a value in the form db_name/table_name before creating the full-text index. The stopword table must have a single VARCHAR column named value.
Here is the process how you can do it-
Create a table to store your keywords-

mysql > CREATE TABLE my_stopwords(value VARCHAR(30)) ENGINE = INNODB;

Now insert stopwords into this table

mysql > INSERT INTO my_stopwords(value) VALUES ('stopword1');

Now Set the innodb_ft_server_stopword_table option to the new stopword table

mysql > SET GLOBAL innodb_ft_server_stopword_table = 'database_name/my_stopwords';

That’s it. Your new stopword list is ready.

NOTE- By default, words less than 3 characters in length or greater than 84 characters in length do not appear in an InnoDB full-text search index. Maximum and minimum word length values are configurable using the innodb_ft_max_token_size and  innodb_ft_min_token_size variables.

Stopwords for MyISAM Search Indexes

To override/alter the default stopword list for MyISAM tables, set the ft_stopword_file system variable.
The variable value should be the pathname of the file containing the stopword list, or the empty string to disable stopword filtering. The server looks for the file in the data directory unless an absolute path name is given to specify a different directory. 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, separating stopwords with any nonalphanumeric character such as newline, space, or comma. Exceptions are the underscore character (_) and a single apostrophe (‘) which are treated as part of a word.
The following table shows the default list of stopwords for MyISAM search indexes.