Home / Indexes / Full Text Indexes / Question No: 171

Can you explain, what are fulltext natural language search, fulltext boolean search and fulltext query expansion searches in MySQL?

MySQL provides three types of full-text searches with IN BOOLEAN MODE and WITH QUERY EXPANSION modifiers. MySQL allows to perform full-text search using MATCH() ... AGAINST() syntax. MATCH takes column names in comma-separated fashion to be searched. AGAINST takes a string to search for, and an optional modifier that indicates what type of search to perform.

FULLTEXT - Natural Language Search

A natural language search interprets the search string as a phrase in natural human language (a phrase in free text). There are no special operators. The stopword list applies. In addition, words that are present in 50% or more of the rows are considered common and do not match. Full-text searches are natural language searches if no modifier is given.

FULLTEXT - Boolen Search

A boolean search interprets the search string using the rules of a special query language. The string contains the words to search for. It can also contain operators like "+", "-" etc that specify requirements such that a word must be present or absent in matching rows, or that it should be weighted higher or lower than usual. Common words such as "some" or "then" are stopwords and do not match if present in the search string. The IN BOOLEAN MODE modifier specifies a boolean search.

FULLTEXT - Query Expansion Search

A query expansion search is a modification of a natural language search. The search string is used to perform a 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 WITH QUERY EXPANSION modifier specifies a query expansion search.