MYSQL – WHAT IS FULLTEXT SEARCH?

I do remember my old days when I am new to MySQL and Web industry. At that point of time whenever I have to implement search functionality I was used to implement it via LIKE keyword as below:

SELECT * FROM tbl_name WHERE col_name LIKE “%search_term%”

At that time I am not aware of full-text search. When I explore through Full-Text search I laugh at myself just because of silly stuff I was doing. I found Full-Text search is much powerful, efficient, faster and accurate way to present results of search query.

To perform Full-Text search you need to define indexing on required columns.

There are few constrain to use Full-Text search:

1. Full-Text index can be used by MyISAM storage engine only
2. Full-Text index can be defined on CHAR, VARCHAR and TEXT datatype only.
3. Full-Text index is an index type of FULLTEXT

Basic syntax of full-text search function is:

MATCH(col1, col2, col3, ….) AGAINST (search_expr [search_modifier])

in above, MATCH takes comma separated column’s name and AGAINT takes string to be searched.

There are three different types of full-text searches available in MySQL

1. Boolean Search
2. Natural Language Search
3. Query Expansion Search

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:

SELECT * FROM tbl_name WHERE MATCH(col1, col2) AGAINST (‘+term1 -term2′ IN BOOLEAN MODE);

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. TheWITH 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.