EDUCBA

EDUCBA

MENUMENU
  • Blog
  • Free Courses
  • All Courses
  • All in One Bundle
  • Login
Home Data Science Data Science Tutorials MySQL Tutorial MySQL FULLTEXT

MySQL FULLTEXT

By Payal UdhaniPayal Udhani

MySQL-FULLTEXT

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.

Start Your Free Data Science Course

Hadoop, Data Science, Statistics & others

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:

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:

MySQL FULLTEXT - 2

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:

full-text index

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:

MySQL FULLTEXT - 4

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:

Developer table

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.

  1. MySQL Database Repair
  2. Types of MySQL Subquery
  3. MySQL Constraints
  4. IF Statement in MySQL
All in One Excel VBA Bundle
500+ Hours of HD Videos
15 Learning Paths
120+ Courses
Verifiable Certificate of Completion
Lifetime Access
Financial Analyst Masters Training Program
1000+ Hours of HD Videos
43 Learning Paths
250+ Courses
Verifiable Certificate of Completion
Lifetime Access
All in One Data Science Bundle
1500+ Hour of HD Videos
80 Learning Paths
360+ Courses
Verifiable Certificate of Completion
Lifetime Access
All in One Software Development Bundle
3000+ Hours of HD Videos
149 Learning Paths
600+ Courses
Verifiable Certificate of Completion
Lifetime Access
Primary Sidebar
All in One Data Science Bundle1500+ Hour of HD Videos | 80 Learning Paths | 360+ Courses | Verifiable Certificate of Completion | Lifetime Access
Financial Analyst Masters Training Program1000+ Hours of HD Videos | 43 Learning Paths | 250+ Courses | Verifiable Certificate of Completion | Lifetime Access
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Corporate Training
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Database Management
  • Machine Learning
  • All Tutorials
Certification Courses
  • All Courses
  • Data Science Course - All in One Bundle
  • Machine Learning Course
  • Hadoop Certification Training
  • Cloud Computing Training Course
  • R Programming Course
  • AWS Training Course
  • SAS Training Course

ISO 10004:2018 & ISO 9001:2015 Certified

© 2023 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA Login

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more