MySQL FULLTEXT SEARCH (FTS)

Full-text search is a searching technique used to examine all words in the document that might not perfectly match the search criteria. The records contain textual data like product descriptions, blog posts, articles, etc. MySQL provides support for full-text indexing and searching from version 3.23.23. In this section, we are going to learn the introduction of FULLTEXT search, syntax, advantages, disadvantages, features, and its types.

The FTS searching technique is commonly used by the search engines such as Google, Mozilla, or Bing. All these search engines collect data from websites into databases and perform searching based on keywords. It makes a powerful search result on websites like Blogs, News, E-commerce, etc.

For example, we have searched for Plant and Trees. The FTS examines both words and can return results that contain the searched words separately, the words in a different order, either Plant and Tree or Tree and Plant, or just Plant and Tree. It makes an advantage to the user in guessing what they want and returning the relevant results faster.

Technically, MySQL usually supports partial text lookups by using the LIKE and regular expression operators. However, these requests have some limitations on large datasets, i.e., when the text column is large or the number of rows increases. It also underperforms when the user's input does not match exactly or might produce no results even the documents contains relevant information with this input.

Important points about full-text searching in MySQL:

• Performance: MySQL needs to scan the whole table to find the exact match based on a pattern specifies in the LIKE or regular expressions statements.
• Flexible Search: MySQL does not have a flexible search query because LIKE and regular expressions examine the search based on pattern matching. For example, to find products whose descriptions contain bike but not classic.
• Relevance Ranking: It does not have any specific way to specify which row in the result set is more relevant to the search keywords.
• Storage Engines: Full text does not support all storage engines. It only supports the MyISAM and InnoDB storage engines in MySQL.

Syntax

MySQL performs full-text searching using the below syntax:

In this syntax, we will first specify the MATCH() function that contains the list of column names separated by a comma to be searched. Then, AGAINST() function takes a string to search with an optional modifier to indicate what type of search is performed. The search_modifier can be IN NATURAL LANGUAGE MODE or IN NATURAL LANGUAGE MODE WITH QUERY EXPANSION or IN BOOLEAN MODE or WITH QUERY EXPANSION.

MySQL FULLTEXT Search Features

The following are some important features of MySQL full-text search:

• Native SQL-like Interface: MySQL performs full-text searching using the SQL-like statement.
• Fully Dynamic Index: Whenever the data modification occurs in the column, MySQL automatically updates the index of that text column.
• Moderate Index Size: The FULLTEXT index size is relatively small.
• Speed: Full-text searching technique is fast that examine the data based on complex search queries.

FULLTEXT Restrictions

• Full text supports only InnoDB and MyISAM table.
• It does not support partition table also.
• The MATCH( ) function parameters must be the same column list from the table that is part of the FULLTEXT index definition unless the MATCH( ) is IN BOOLEAN MODE on a MyISAM table.
• We cannot use the '%' wildcard character for full-text searches.
• The argument to AGAINST( ) should be a constant string value during the query execution.
• The full-text index uses the same character set and collation for all columns.
• We can create the full-text indexes for CHAR, VARCHAR, or TEXT columns only.

FULLTEXT Search Types

The full-text search is divided mainly into three types:

Natural Language Search Type

This search mode interprets the search string as a literal phrase in natural human language. It does not support special characters. If no modifier is specified or when the IN NATURAL LANGUAGE MODE modifier is specified, it is enabled by default.

Query Expansion Search Type

It is a modification of the natural language search type that performs the search twice. It first performs the natural language search that includes a few most relevant documents and then searches again to return the result. It is enabled by using the WITH QUERY EXPANSION modifier.

Boolean Search Type

This search mode interprets the search string for complex queries that can include Boolean operators such as less than (<) or more than (>) operators, the plus (+) and minus (-) sign, subexpressions ("(" and ")"), double quotes (""), an operator that lowers the value's contribution to the results (~) and the wildcards. It is enabled by using the IN BOOLEAN MODE modifier.

Let us learn how we can define a full-text index to perform full-text searches in MySQL.

We have to ensure that it is required to index its data before performing a full-text search on the table's column. Whenever the column data changes, MySQL will recreate the full-text index automatically. In MySQL, the full-text index is always named FULLTEXT. We can define the full-text index whose column data type is CHAR, VARCHAR, and TEXT.

We can define the full-text index while creating the table using CREATE TABLE statement or using the ALTER TABLE or CREATE INDEX statement for the existing table.

Create FULLTEXT Index using CREATE TABLE Statement

The full-text index is defined while creating a new table using the CREATE TABLE statement as follows:

Here the table_name is the name of a new table, column_name is the name of a column, and FULLTEXT indicates the full-text index that contains a list of comma-separated column names in parentheses.

The below statement creates a new table named Articles. This table has a FULLTEXT index that includes the article content column:

Next, we will fill records into this table:

We will now perform a full-text search using the MATCH() function that contains a search string in the AGAINST() argument. It performs searching in case-insensitive mode. The MATCH() function returns a relevant value for each row in the table. If we use the MATCH() function in the WHERE clause, the returned row is sorted automatically with the highest relevance value first. Relevance is computed based on the number of words, the number of unique words, the total number of words in the collection, and the number of rows that contain a particular word.

Here is the output where we have performed the full-text search against a string "tutorial":

Let us see another example that shows how to retrieve the relevant values explicitly:

Here is the output where returned rows are not ordered:

Create FULLTEXT Index using ALTER TABLE Statement

MySQL also provides the ALTER TABLE statement to create a full-text index on the existing table. The following syntax illustrates it more clearly:

In this syntax, we need to first specify the table name to create an index. Second, use the ADD FULLTEXT clause to define the full-text index to one or more columns.

For example, we have a table named books that contain columns id, title, content, and author. Now, we can define the full-text index for the content and author columns as follows:

Create FULLTEXT Index using CREATE INDEX Statement

We can also create a full-text index for the existing table by using the CREATE INDEX statement. The following syntax illustrates it more clearly:

For example, we have a table named office that contains columns address_line1 and address_line2. Now, we can create a FULLTEXT index for these columns using the following statement:

How to drop a FULLTEXT index?

MySQL provides a command to drop the full-text index from the table. We can do this by using the ALTER TABLE DROP INDEX statement as given below:

In this syntax, we first specified the name of a table and then the full-text index name followed by the DROP INDEX clause.

For example, executing the below statement will permanently remove the address index from the "offices" table: