Stopwords are used to provide a list of commonly-used words that can be ignored for the purposes of Full-text-indexes.

Full-text indexes built in MyISAM and InnoDB have different stopword lists by default.

MyISAM Stopwords

For full-text indexes on MyISAM tables, by default, the list is built from the file storage/myisam/ft_static.c, and searched using the server's character set and collation. The ft_stopword_file system variable allows the default list to be overridden with words from another file, or for stopwords to be ignored altogether.

If the stopword list is changed, any existing full-text indexes need to be rebuilt

The following table shows the default list of stopwords, although you should always treat storage/myisam/ft_static.c as the definitive list. See the for more details, and for related articles.

InnoDB Stopwords

Stopwords on full-text indexes are only enabled if the system variable is set (by default it is) at the time the index was created.

The stopword list is determined as follows:

• If the system variable is set, that table is used as a stopword list.

• If innodb_ft_user_stopword_table is not set, the table set by is used.

• If neither variable is set, the built-in list is used, which can be viewed by querying the in the .

In the first two cases, the specified table must exist at the time the system variable is set and the full-text index created. It must be an InnoDB table with a single column, a named VALUE.

The default InnoDB stopword list differs from the default MyISAM list, being much shorter, and contains the following words:

_{This page is licensed: CC BY-SA / Gnu FDL}

MariaDB has support for full-text indexing and searching:

• A full-text index in MariaDB is an index of type FULLTEXT, and it allows more options when searching for portions of text from a field.

• Full-text indexes can be used only with MyISAM, Aria, InnoDB and Mroonga tables, and can be created only for CHAR, VARCHAR, or TEXT columns.

• Partitioned tables cannot contain fulltext indexes, even if the storage engine supports them.

• A FULLTEXT index definition can be given in the statement when a table is created, or added later using or .

• For large data sets, it is much faster to load your data into a table that has no FULLTEXT index and then create the index after that, than to load data into a table that has an existing FULLTEXT index.

Full-text searching is performed using syntax. MATCH() takes a comma-separated list that names the columns to be searched. AGAINST takes a string to search for, and an optional modifier that indicates what type of search to perform. The search string must be a literal string, not a variable or a column name.

Excluded Results

• Partial words are excluded.

• Words less than 4 (MyISAM) or 3 (InnoDB) characters in length will not be stored in the fulltext index. This value can be adjusted by changing the system variable (or, for , ).

• Words longer than 84 characters in length will also not be stored in the fulltext index. This values can be adjusted by changing the system variable (or, for , ).

Relevance

MariaDB calculates a relevance for each result, based on a number of factors, including the number of words in the index, the number of unique words in a row, the total number of words in both the index and the result, and the weight of the word. In English, 'cool' will be weighted less than 'dandy', at least at present! The relevance can be returned as part of a query simply by using the MATCH function in the field list.

Types of Full-Text search

IN NATURAL LANGUAGE MODE

IN NATURAL LANGUAGE MODE is the default type of full-text search, and the keywords can be omitted. There are no special operators, and searches consist of one or more comma-separated keywords.

Searches are returned in descending order of relevance.

IN BOOLEAN MODE

Boolean search permits the use of a number of special operators:

Searches are not returned in order of relevance, and nor does the 50% limit apply. Stopwords and word minimum and maximum lengths still apply as usual.

WITH QUERY EXPANSION

A query expansion search is a modification of a natural language search. The search string is used to perform a regular natural language search. Then, words from the most relevant rows returned by the search are added to the search string and the search is done again. The query returns the rows from the second search. The IN NATURAL LANGUAGE MODE WITH QUERY EXPANSION or WITH QUERY EXPANSION modifier specifies a query expansion search. It can be useful when relying on implied knowledge within the data, for example that MariaDB is a database.

Examples

Creating a table, and performing a basic search:

Multiple words:

Since 'Once' is a , no result is returned:

Inserting the word 'wicked' into more than half the rows excludes it from the results:

Using IN BOOLEAN MODE to overcome the 50% limitation:

Returning the relevance:

WITH QUERY EXPANSION. In the following example, 'MariaDB' is always associated with the word 'database', so it is returned when query expansion is used, even though not explicitly requested.

Partial word matching with IN BOOLEAN MODE:

Using boolean operators

See Also

• For simpler searches of a substring in text columns, see the operator.

_{This page is licensed: CC BY-SA / Gnu FDL}