Full text searching is performed by operating on words and phrases based on rules of a particular language. A full text index on column that contains char, varchar and nvarchar data can be built. Full text indexes can also be built on columns containing formatted binary data. For full text index, the table must have a single, unique not null column

Explain the three types of full-text searches.

boolean search – Full text searches can be done in Boolean mode as well.

SELECT * FROM product WHERE MATCH (product_color)
AGAINST (‘+Blue –red’ IN BOOLEAN MODE);

Here, + means the word is required to be present while – indicates that the word requires to be absent. This means that + can be considered as AND while – can be considered as NOT in Boolean search. Boolean text search does not automatically sort rows in order of decreasing relevance.

Natural language- The Match() function by default performs a natural language search.

CREATE TABLE product (
Id INT(10),
Color VARCHAR(200),
Description TEXT,
FULLTEXT(color,Description)
);

To search for products with ALL colors and famous in their description
SELECT * from product
WHERE MATCH (color, description) AGAINST (‘famous’);

The search is performed in a case insensitive fashion.

query expansion- This type of expansion is generally used when the user wants to search for a short phrase. Blind query expansion is enabled by adding WITH QUERY EXPANSION phrase. It serahes twice where the search phrase for the second search is the original search phrase concatenated with the few most highly relevant documents from the first search.

Example:

SELECT * FROM product WHERE MATCH (product_color, description) AGAINST (‘Blue’ WITH QUERY EXPANSION);

Here, we intend to find products with blue color.

What are the Full-Text Restrictions?

Full – Text searches are supported by MyISAM tables only.
FULL TEXT parser cannot work for languages like Chinese or Japanese where there is no word delimiter.
All columns in a FULLTEXT index must use the same character set.
The argument passed to AGAINST () must be a constant string.