Full Text Index in MySQL

Introduction

A Full-Text Index in MySQL is a specialized data structure that enables efficient and fast searching of text-based data. It is designed to handle large amounts of unstructured text data, including articles, blog posts, user-generated content, or any other form of textual information.

Full-Text Index in MySQL works by breaking up the text data into individual words, removing stop words (words which do not add much meaning to the text like the, and, or, a, an, in, on), and then indexing the remaining words by keeping track of their positions within the text. In place of exact matches within a given column, this enables users to search for data based on keywords or phrases. The search results are then ranked based on relevance to the query, with more relevant results appearing at the top of the list.

The main purpose of a full-text index is to provide better search functionality for text-based data, particularly for applications that need to analyze and search large volumes of text-based data. This includes content management systems, e-commerce websites, and search engines.

For example, an E-commerce website like Amazon uses a Full Text Index to enable users to search for products based on keywords in the product descriptions or user-generated reviews. A content management system might use a Full Text Index to enable users to search for articles/blogs based on keywords. A search engine might use a Full Text Index to enable users to search for information across multiple websites based on keywords or phrases.

How to Create a Full-Text Index?

To create a Full Text Index in MySQL, you can use the CREATE FULLTEXT INDEX command. This command specifies the columns to be indexed and other optional parameters, such as the minimum word length or the language used in the text. Once the index is created, you can use the MATCH() and AGAINST() functions in queries to search for text-based data based on keywords or phrases.

It is generally recommended to avoid adding the Full Text Index immediately when creating a table, as the insertion of large amounts of data can be performed more efficiently without an index. As a result, it is often preferable to add the index later using the ALTER TABLE or CREATE INDEX commands. While MyISAM tables have always supported full-text indexes, InnoDB tables added support for it starting from version 5.6.4.

Syntax and Usage

A Full-Text Index in MySQL can be created using the following syntax:

While creating a Full Text Index in MySQL, we can choose a name for the index in place of index_name, specify the table name in place of table_name where we want the index to be created, and include one or more column names in the index. To add multiple columns to the index, we can simply separate them using commas.

The CREATE TABLE statement can also be used to define the Full Text Index while creating a new table as:

Here, column_name denotes the name of a column, table_name denotes the name of the new table, and FULLTEXT denotes the Full Text Index, which includes a list of comma-separated column names within the parentheses.

Full-Text Index and Performance

In general, the performance of the Full Text Index in MySQL is fast and effective for text-based searching. This is because an inverted data structure is used by Full Text Index which maps words to their respective locations in the indexed columns. When a user searches a text based on keywords/phrases, MySQL searches the index and returns the relevant results based on their relevance to the search terms.

The performance of Full-Text Index in MySQL, however, can differ based on several factors, including the size of the indexed columns, the complexity of the search words, and the hardware resources available. We can adhere to certain best practices to optimize the performance of Full Text Index in MySQL, such as:

• Selecting the right data types for the indexed columns to reduce the storage space and improve search performance.
• Eliminating stop words that do not add much meaning to the text (like a, an, the, in, on etc.) for reducing the size of the index and thus, improving search performance
• Another best practice to optimize the performance of Full Text Index in MySQL is to tune its parameters, such as minimum word length, to improve search performance. By adjusting these parameters, we can optimize the full-text index to better match the specific needs of our application and improve its search efficiency.
• Choosing appropriate hardware resources, including CPU, memory, and storage, to handle large volumes of data and queries.

Querying Full-Text Index

When we create a Full Text Index in MySQL on one or more columns of a table, MySQL creates a special data structure that indexes the words within these columns. This allows us to search for specific words or phrases within the indexed columns using the MATCH AGAINST syntax in our queries.

Here is an example syntax for performing a full-text search query in MySQL:

In this syntax, "table_name" is the name of the table that contains the indexed column(s) you want to search, "column_name" is the name of the specific indexed column(s) you want to search, and "search term" is the word or phrase you want to search for.

To illustrate with an example, suppose we have a table called "blogs" with a Full Text Index on the "information" column. We can search for blogs that contain the word "MySQL" by executing the following query:

This query will return all blogs from the "blogs" table that contain the word "MySQL" within the "information" column.

• We can also use the MATCH AGAINST syntax in combination with other search conditions in our queries, such as the WHERE clause and Boolean operators. This allows us to perform more complex text-based searches on our data.

MySQL provides several Boolean operators and search modifiers that we can use to refine our full-text searches. For example, we can use the "+" operator to require a specific word to be present in the search results, or the "-" operator to exclude specific words. We can also use search modifiers such as IN BOOLEAN MODE to enable Boolean full-text searches.

This query will return all rows in the "products" table that contain the word "SQL" in the "Desc" column.

• When we execute a full-text search query in MySQL, the search results are ranked based on their relevance to the searched terms. MySQL provides ranking information in the form of a relevance score, which is a decimal value between 0 and 1 that indicates the relevance of each searched result. We can use this relevance score to further refine our search results.

Here is an example of using the relevance score to sort the search results in descending order of relevance:

In this query, the MATCH AGAINST clause has been used to calculate the relevance score for each search result, which is then returned as a column named "rel". The ORDER BY clause is used to sort the search results in descending order of relevance.

Full-Text Index Limitations

Although Full-Text Index in MySQL is a powerful tool for text-based searching, it has some limitations that can impact its functionality in certain scenarios. Here are some of the limitations of Full-Text Index in MySQL:

• Full-Text Indexing is supported only in InnoDB and MyISAM tables in MySQL.
• For all columns, the Full Text Index in MySQL uses the same character set and collation.
• It is only possible to create Full-Text Indexes for columns with data types CHAR, VARCHAR, or TEXT in MySQL.
• Full-Text Index in MySQL can only search within one or more specified columns and cannot search across multiple tables or databases.
• Adding a full-text index to a MySQL database can considerably increase its size, leading to potential performance and scalability issues for the application.
• Full-Text Index in MySQL is optimized for matching full words or phrases and does not work well for partial matches or misspelled words.
• The relevance ranking algorithm used by the Full-Text Index in MySQL relies solely on the frequency of the search terms within the indexed columns and does not consider other factors like word proximity or semantic relevance. As a result, some queries may not produce highly accurate relevance rankings.
• Full-Text Index in MySQL only supports a limited set of natural languages, so it may not be suitable for applications that require searching in multiple languages.

Altering a Table with Full-Text Index

The ALTER TABLE command in MySQL can also be used to add a Full-Text Index to an existing table. Here is an example syntax for adding a Full Text Index in MySQL using ALTER TABLE statement:

Here, "table_name" is the name of the table we want to alter and we have used the ADD FULLTEXT clause to define the Full Text Index to one or more columns.

When we alter a table to add/modify a Full-Text Index, MySQL will automatically re-index the data in the table, which can take some time while working with large tables. Once the Full Text Index is created or modified, we can then use the MATCH AGAINST syntax to search for data within the indexed columns.

To illustrate, consider the "products" table in a sample database. If you want to enable full-text search capabilities for the "products" and "product-type" columns in this table, you can define a FULLTEXT Index on these columns using the appropriate syntax in MySQL which is as follows:

Dropping a Full-Text Index

Dropping a full-text index in MySQL is a straightforward process that involves using the DROP INDEX statement. This type of index is created using the CREATE INDEX statement, and once it's no longer needed, it can be removed by executing the DROP INDEX statement with the appropriate syntax.

Here's an example syntax for dropping a Full Text Index in MySQL:

In this syntax, "table_name" is the name of the table that contains the full-text index, and "index_name" is the name of the full-text index we want to drop from the table.

While dropping a Full Text Index in MySQL, it's essential to keep in mind that this action is irreversible, and all the associated data structures will be permanently deleted. As a result, it's crucial to be certain that we intend to drop the index before executing the DROP INDEX statement.

To illustrate using an example, the following statement removes the state index from the country table: