EDUCBA

Home » Data Science » Data Science Tutorials » MySQL Tutorial » MySQL FULLTEXT

MySQL-FULLTEXT

Introduction to MySQL FULLTEXT

Fulltext is the searching facility that allows the users to search for certain keys that are not even mentioned perfectly and helps in retrieving the searched filtered data in a fast and easy way. The full-text search is most often used by search engines such as Google or bing and also 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 the storage engines MyISAM and InnoDB. In this article, we will learn about the working of full-text search and see how we can define full-text search in MySQL with the help of examples.

How FULLTEXT works in MySQL?

  • Fulltext search technique makes the use of the entities of databases called documents that have textual data in it such as the name of customers, items, descriptions, and then search based on keywords. For example, when a search such as Pant and Shirt is made then the retrieved searched resultset includes the result containing pant word separately and result of shirt word separately and also the results that contain shirt and pant together in any of the order in it like shirt and pant or pant and shirt togetherly in the content.
  • Even though the features like regular expressions and like the statement is available in MySQL, they are not sufficient for the search when the column length is more or the search is to be made on multiple column content. Using the full-text search provides many advantages such as performance of search, flexibility, and ranking based on relevance.
  • The performance of like and regular expressions used for searching is very less as all the contents of the table are needed to be searched. Also, the flexibility of the search provided by like statements and regular expressions is not that effective. For example, when we have to find the resultset such that it will contain a shirt but not tie in it is not possible. While full-text search uses sophisticated algorithms to search that is done using a full-text index generated from the text data and lookup index of the table.

Features of MySQL FULLTEXT

Some of the other features of full-text search in MySQL include Native SQL like interface, dynamic indexing, small index size, and high-speed search. SQL like the statement is provided in MySQL 5.6 and higher versions to perform the full-text search. Whenever any of the data of the columns on which full-text index is created is changed then the full-text index is automatically updated in MySQL. The full-text index that is created to perform full-text searches is relatively small in size.

Start Your Free Data Science Course

Hadoop, Data Science, Statistics & others

Creating Fulltext Index

We can create the MySQL full-text index that has the default index name as FULLTEXT. There are three ways in which the full-text index can be created in MySQL in the particular table that are listed below –

  • Using CREATE TABLE statement while new table creation.
  • Using the ALTER TABLE statement while defining the FULLTEXT index on an existing table.
  • 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 customers 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

We can see that the full-text index has been defined on the description column and the query has executed successfully but gives one warning saying that the InnoDB table in our case subjects has been rebuild to add a column 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 existing Developer table using CREATE FULLTEXT INDEX in following query statement –

Code:

CREATE FULLTEXT INDEX email_index
ON Developer(email);
show warnings;

Output:

MySQL FULLTEXT - 4

We can see that the full-text index has been defined on the description column and the query has executed successfully but gives one warning saying that the InnoDB table in our case Developer has been rebuild to add a column FTS_DOC_ID for Full-Text Search. We can list out the keys in 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

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 using the three ways mentioned above. 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

This is a guide to MySQL FULLTEXT. Here we discuss an introduction to MySQL FULLTEXT with appropriate syntax, features, and query examples. You can also go through our other related articles to learn more –

  1. MySQL Database Repair
  2. MySQL Subquery
  3. MySQL Constraints
  4. IF Statement in MySQL
0 Shares
Share
Tweet
Share