Updated May 23, 2023
Introduction to MySQL FULLTEXT
Fulltext is the search facility that allows the users to search for specific keys that are not even mentioned perfectly and helps retrieve the searched filtered data quickly and easily. The full-text search is often used by search engines such as Google or Bing and in blog post searching, e-commerce site search, etc. In Mysql, the full-text search facility is provided to search for the matching text in one or more column data. Fulltext search support is only found in Mysql 5.6 and higher versions for MyISAM and InnoDB storage engines. In this article, we will learn about the working of full-text search and how we can define full-text search in MySQL with the help of examples.
How FULLTEXT works in MySQL?
The fulltext search technique uses the entities of databases called documents with textual data, such as the name of customers, items, and descriptions, and then searches based on keywords. For example, when a search such as Pant and Shirt is made, then the retrieved searched resultset includes the result containing the pant word separately and the result of shirt word separately and also the results that have shirt and pant together in any of the order in it like shirt and pant or pant and shirt togetherly in the content.
The available features in MySQL, such as regular expressions and the “LIKE” statement, may not be adequate for effective searches in scenarios where the column length is extensive or when searching across multiple-column contents. The full-text search provides many advantages, such as search performance, flexibility, and ranking based on relevance.
The performance of searching using “LIKE” and regular expressions is significantly lower since it requires searching through the entire table’s contents. Also, the flexibility of the search provided by like statements and regular expressions is ineffective. For example, when we have to find the resultset such that it will contain a shirt but not a tie-in, it is not possible. The full-text search employs sophisticated algorithms to perform searches by utilizing a generated full-text index derived from the text data and a table lookup index.
Features of MySQL FULLTEXT
Some other features of full-text search in MySQL include a Native SQL-like interface, dynamic indexing, small index size, and high-speed search. MySQL 5.6 and higher versions support the “LIKE” statement for performing full-text searches. Whenever there are modifications to the data in columns with a full-text index, the index is automatically updated in MySQL. The full-text index created for these searches typically has a compact size.
Creating Fulltext Index
We can create the MySQL full-text index with the default FULLTEXT index. You can create a full-text index in MySQL on a particular table using one of the following three methods:
- Using CREATE TABLE statement while new table creation.
- To define a FULLTEXT index on an existing table using the ALTER TABLE statement
- Using CREATE INDEX statement while defining the FULLTEXT index on an existing table.
Examples to Implement MySQL FULLTEXT
Let us see each of the methods with the help of an example:
Example #1
Using CREATE TABLE statement while new table creation
We can define the FULLTEXT index on a particular column while creating a new table by using the following syntax –
Syntax
CREATE TABLE name_of_table(
list_of_column,
...,
FULLTEXT (columnNo1,columnNo2,..)
);We can mention the list of the columns on which you want to create the FULLTEXT index in the comma-separated column names like above columnNo1,columnNo2,…
For example, We can create a full-text index on the address column on the customer’s table while creating the table using the following query statement –
Code:
CREATE TABLE customers (
id INT NOT NULL AUTO_INCREMENT,
name VARCHAR(255) NOT NULL,
address TEXT,
PRIMARY KEY (id),
FULLTEXT KEY (address )
);Output after execution:
Example #2
Using the ALTER TABLE statement while defining the FULLTEXT index on the existing table
We can define the FULLTEXT index on a particular column on the existing table by using the following syntax –
Syntax
ALTER TABLE name_of_table
ADD FULLTEXT(columnNo1,columnNo2,..)We can mention the list of the columns on which you want to create the FULLTEXT index in the comma-separated column names like above columnNo1,columnNo2,…
For example, We can create a full-text index on the description column on the existing subjects table using the following query statement –
Code:
ALTER TABLE subjects
ADD FULLTEXT(description);
show warnings;Output after execution:
The query was successfully executed, indicating that the description column now has a defined full-text index. However, a warning indicates that the InnoDB table “subjects” underwent a rebuild to incorporate a new column named FTS_DOC_ID for Full-Text Search.
Example #3
Using CREATE INDEX statement while defining the FULLTEXT index on an existing table
We can define the FULLTEXT index on a particular column on an existing table by using the CREATE INDEX statement that has the following syntax –
Syntax:
CREATE FULLTEXT INDEX name_of_index
ON name_of_table(index_column,...)We can mention the list of the columns on which you want to create the FULLTEXT index in the comma-separated column names like above columnNo1,columnNo2,…
we have one table named Developer that has the following description output after executing DESC Developer; command –
Code:
desc Developer;Output:
We can create a full-text index on the email column named email_index on the existing Developer table using CREATE FULLTEXT INDEX in the following query statement –
Code:
CREATE FULLTEXT INDEX email_index
ON Developer(email);
show warnings;Output:
The query was executed successfully, confirming the definition of the full-text index in the description column. However, a warning indicates that the InnoDB table, specifically “Developer,” underwent a rebuild to incorporate a new column called FTS_DOC_ID for Full-Text Search. We can list out the keys in the Developer table to see the name of the key that we have created for full text on the email column using the following query –
Code:
show keys in Developer;Output:
Conclusion
Using the abovementioned methods, we can define the full-text keys on one or more columns in Mysql MyISAM and InnoDB tables for MySQL version 5.6 or higher. Fulltext search greatly speeds up the searching facility on the table contents in MySQL and can be used with the help of native SQL-like statements easily.
Recommended Articles
We hope that this EDUCBA information on “MySQL FULLTEXT” was beneficial to you. You can view EDUCBA’s recommended articles for more information.
