MySQL Fulltext Search

What is Fulltext Search in MySQL?

MySQL is one of the most popular and widely used relational database management systems (RDBMS). It provides various search capabilities to perform efficient and flexible searches on the data stored in the database. MySQL fulltext search is one of the search capabilities present in MySQL that allows users to search for specific words or phrases within the text fields of a table.

In simpler terms, mysql fulltext search is a search mechanism that allows users to perform searches for text-based data stored in a MySQL database. This search mechanism uses an index to locate relevant rows that contain the searched terms quickly. After discovering the relevant row, it returns the results in a relevant and meaningful order.

Important Points About Full-Text Searching in MySQL

MySQL Fulltext Search is a powerful search mechanism for efficient and relevant searches on text-based data stored in a MySQL database. The following are some important points that should be considered while using MySQL Fulltext Search:

Performance

MySQL Fulltext Search can be a high-performance search mechanism when implemented correctly. We can search faster than traditional LIKE searches by using Fulltext Indexes on text-based columns. Fulltext Indexes can improve the performance of search queries, especially when the database contains a large number of rows.

For example, let's say you have a table of product descriptions and want to search for all products containing the word waterproof. A Fulltext Search can be performed on the description column with a Fulltext Index, resulting in a much faster search than using a LIKE query.

Flexible Search

MySQL fulltext search is also flexible and powerful, allowing users to perform complex searches and filtering on the data stored in the database. It supports various search operators, such as boolean operators, proximity search, and phrase search. This enables the users to refine their searches and locate specific data quickly.

For example, you can search for products containing the phrase waterproof case or search for products containing the words waterproof and case but not bag.

Relevance Ranking

Another important feature of MySQL fulltext search is relevance ranking. This feature allows MySQL to return the most relevant search results first based on how well the search terms match the data in the database. This ranking algorithm considers various factors, such as the frequency of the search terms, their location within the text, and their proximity to each other.

For example, let's say you are searching for products containing the word waterproof. The search results will be returned with the most relevant products first, such as those with the word "waterproof" in the title or description.

Storage Engines

MySQL fulltext search is supported by several storage engines, including MyISAM and InnoDB. These storage engines provide different performance and functionality benefits depending on the specific use case and data being stored. MyISAM is the most widely used storage engine for fulltext search due to its performance and indexing capabilities.

For example, if you have a large database with InnoDB storage engine, you can use Fulltext Indexes to enable efficient and fast search capabilities.

Syntax The syntax for fulltext search in MySQL is relatively simple and straightforward. To perform a fulltext search, you need to use the MATCH() function, followed by the AGAINST() function.

In this example, tableName is the name of the table containing the data to be searched, column_name is the name of the column to be searched, and "mysql fulltext search" is the search query.

MySQL FULLTEXT Search Features

MySQL FULLTEXT Search comes with a variety of features that make it a powerful search tool for text-based data. The following are some of the key features of MySQL FULLTEXT Search:

• Natural Language Search: This search type allows users to perform searches using natural language. It is the default search type in MySQL and can be used to find relevant results based on the relevance of the search terms. For example, let's say you are searching for products containing the "waterproof case" phrase. A Natural Language Search will find all products that contain both "waterproof" and "case" in their title or description, ordered by relevance.
• Boolean Search: This search type allows users to perform complex searches using boolean operators such as AND, OR, and NOT. Boolean search is useful when searching for specific combinations of words or when trying to exclude certain words from search results. For example, you can use Boolean Search to find products that contain the word "waterproof" but not the word "bag".
• Query Expansion Search: This search type expands the search query to include synonyms of the search terms. This can be useful when users are unsure of the specific terms to use in a search query. For example, let's say you are searching for products containing the word "waterproof". The Query Expansion Search will also search for synonyms of "waterproof", such as "water-resistant" or "hydrophobic", to return more comprehensive search results.
• Phrase Search: This search type allows users to search for exact phrases by enclosing the search terms in double quotes. For example, if you are searching for products containing the exact phrase "waterproof phone case", a Phrase Search will only return results that include the exact phrase.
• Stopwords: Stopwords are common words such as a', an', the, and in that are ignored by MySQL FULLTEXT Search. This helps to improve search performance and reduce the size of the Fulltext Index.
• Minimum Word Length: By default, MySQL FULLTEXT Search only searches for words that are at least three characters long. This can be adjusted using the ft_min_word_len system variable.
• Creating Complex Search Queries with Boolean and Phrase Search: It's important to note that we can combine these features to create complex and specific search queries. For example, a user could perform a Boolean Search to find all products containing "waterproof" and "case", then use Phrase Search to find products that contain the exact phrase "waterproof phone case".
• Combining MySQL FULLTEXT Search with other MySQL Features: We can use MySQL FULLTEXT search with other MySQL functions and features. For example, it can be combined with the GROUP BY clause to group search results by specific columns or values.
• Partial Word Searches in MySQL FULLTEXT Search with Wildcard: Another useful feature of MySQL FULLTEXT Search is the ability to perform partial word searches. Using the asterisk wildcard, users can search for words containing a specific sequence of characters. For example, searching for "swim" would return results for words such as "swimming", "swimwear", and "swimming pool".
• Using the NEAR Operator in MySQL FULLTEXT Search: MySQL FULLTEXT Search also supports the NEAR operator, which allows users to find results that contain two or more search terms within a specified distance of each other. This is particularly useful for finding results that are contextually related to each other, such as searching for "pizza NEAR delivery" to find restaurants that offer pizza delivery.
• MySQL FULLTEXT Search supports multiple languages: MySQL FULLTEXT Search supports multiple languages, meaning users can perform text-based searches in languages other than English. This feature is handy for international businesses or websites that cater to multilingual audiences.

FULLTEXT Restrictions

Several restrictions apply to fulltext search in MySQL. Some of the most important restrictions are:

1. Minimum Word Length: By default, MySQL FULLTEXT Search does not index words that are less than 3 characters long. This means that words such as the, and, and of are not indexed and cannot be searched. However, we can modify the ft_min_word_len system variable to adjust the minimum word length.

2. Stopword List: MySQL FULLTEXT Search also has a built-in list of stopwords, which are common words that are not indexed and cannot be searched. This list includes words such as a', an', the, and in. However, we can customize the stopword list by modifying the ft_stopword_file system variable.

3. Index Size: Fulltext search can only be performed on columns with the FULLTEXT index. MySQL FULLTEXT Search indexes can become very large, especially when dealing with large datasets or complex search queries. This can affect performance and may require additional server resources to handle.

4. Storage Engines: Not all storage engines support MySQL FULLTEXT Search. In particular, the MyISAM and InnoDB storage engines are the only ones that fully support FULLTEXT indexes.

5. Case Sensitivity: Fulltext search is not case-sensitive by default, but this can be changed using the collation settings.

FULLTEXT Search Types

MySQL fulltext search supports three different search types: natural language search type, query expansion search type, and boolean search type. Each of these search types has its own syntax and features, as discussed below:

Natural Language Search Type

The natural language search type is the default search type used in MySQL fulltext search. It allows users to search using natural language queries, such as find all articles about MySQL fulltext search. The syntax for natural language search is as follows:

Syntax:

The natural language search type supports several features, including stopwords, phrase search, and boolean operators. It also ranks search results based on relevance.

Query Expansion Search Type

The query expansion search type expands the search query to include related words. This can be useful when searching for synonyms or related terms. The syntax for query expansion search is as follows:

Syntax:

The query expansion search type ranks search results based on relevance, similar to the natural language search type.

Boolean Search Type

The boolean search type performs complex searches using boolean operators such as AND, OR, and NOT. This search type allows users to specify more complex search queries than the natural language search type. The syntax for boolean search is as follows:

Syntax:

The boolean search type also supports features such as phrase search, proximity search, and wildcard search. However, it ranks search results differently than the natural language and query expansion search types.

Create FULLTEXT Index Using CREATE TABLE Statement

Users can include the FULLTEXT keyword in the column definition to create a FULLTEXT index using the CREATE TABLE statement in MySQL. The following is an example:

In this example, the articles table has a FULLTEXT index on the title and body columns. This allows users to perform text-based searches on these columns using MySQL FULLTEXT Search.

It is important to note that the column types must be compatible with FULLTEXT indexing. Generally, only columns of type CHAR, VARCHAR, or TEXT can be indexed using FULLTEXT.

Additionally, the FULLTEXT index can be customized using various modifiers, such as the WITH PARSER modifier, which specifies a custom parser for the index. For example:

Query:

In this example, the FULLTEXT index uses the ngram parser, which is designed to handle short and fuzzy search terms.

By creating a FULLTEXT index using the CREATE TABLE statement, users can easily incorporate MySQL FULLTEXT search into their database design and ensure their text-based data is easily searchable.

Create FULLTEXT Index Using ALTER TABLE Statement

In MySQL, users can add a FULLTEXT index to an existing table using the ALTER TABLE statement. This is useful when users need to modify an existing table to include text-based searching capabilities.

To create a FULLTEXT index using the ALTER TABLE statement, users need to specify the table's name and the column or columns that will be indexed. The following is an example:

Query:

In this example, the FULLTEXT index is added to the title and body columns of the articles table. Once the index is created, users can use MySQL FULLTEXT Search to perform text-based searches on these columns.

Create FULLTEXT Index Using CREATE INDEX Statement

In MySQL, users can create a FULLTEXT index using the CREATE INDEX statement. This is useful when users want to add a FULLTEXT index to a table that already has an index, or if they want to create a unique FULLTEXT index on a specific column or set of columns.

To create a FULLTEXT index using the CREATE INDEX statement, users need to specify the name of the index, the name of the table, and the name of the column or columns that will be indexed. The following is an example of the same:

Query:

In this example, a FULLTEXT index is created on the title column of the articles table, and the index is named title_ft_idx. Once the index is created, users can use MySQL FULLTEXT Search to perform text-based searches on this column.

How to Drop a FULLTEXT Index?

To drop a FULLTEXT index, you need to use the ALTER TABLE statement with the DROP INDEX option. The following is an example:

Syntax:

Let us take an example to understand this. Suppose we need to drop the existing FULLTEXT index on the title column of the articles table, and a new index is to be created on the body column only, using the ngram parser. This will allow for more customized text-based searching capabilities and improve search performance. We can achieve this task with the help of the following query: