Boolean Search:
Boolean full-text search is kind of logical expression search. To perform search in boolean mode you need to provide IN BOOLEAN MODE search modifier in above suggested syntax. We need to provide some special meaning character at the beginning or end of the words in search string. A boolean search can be done in the absence of an index (albeit more slowly), in which case it is possible to name columns from multiple tables.
For example:
Following Operators supported in boolean full-text search:
- + : indicates word must be presented into each row
- - : indicates word must not be present into row
- > < : These two operators are used to change a word’s contribution to the relevance value that is assigned to a row. The
>operator increases the contribution and the<operator decreases it - ( ) : Parentheses group words into subexpressions. Parenthesized groups can be nested.
- ~ : A leading tilde acts as a negation operator, causing the word’s contribution to the row’s relevance to be negative
- * : Wildcard search operator similar to % in LIKE statement
Following example demonstrate above operator usage:
- ‘apple banana’
Find rows that contain at least one of the two words. - ‘+apple +juice’
Find rows that contain both words. - ‘+apple macintosh’
Find rows that contain the word “apple”, but rank rows higher if they also contain “macintosh”. - ‘+apple -macintosh’
Find rows that contain the word “apple”, but rank rows higher if they also contain “macintosh”. - ‘+apple -macintosh’
Find rows that contain the word “apple” but not “macintosh”. - ‘+apple ~macintosh’
Find rows that contain the word “apple”, but if the row also contains the word “macintosh”, rate it lower than if row does not. - ‘+apple +(>turnover <strudel)’
Find rows that contain the words “apple” and “turnover”, or “apple” and “strudel” (in any order), but rank “apple turnover” higher than “apple strudel” - ‘apple*’
Find rows that contain words such as “apple”, “apples”, “applesauce”, or “applet”.
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.
it is a requirement that the columns named in the MATCH() function be the same columns included in some FULLTEXT index in your table.
A full-text search that uses an index can name columns only from a single table in the MATCH() clause because an index cannot span multiple tables.
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.
For example, a user searching for “database” may really mean that “MySQL”, “Oracle”, “DB2”, and “RDBMS” all are phrases that should match “databases” and should be returned, too.
Article information brought from mysql.com.
Happy Searching 
Pages: 1 2
