Code360 powered by Coding Ninjas X Naukri.com. Code360 powered by Coding Ninjas X Naukri.com
Last Updated: Mar 27, 2024

MySQL Fulltext Search

Leveraging ChatGPT - GenAI as a Microsoft Data Expert
Speaker
Prerita Agarwal
Data Specialist @
23 Jul, 2024 @ 01:30 PM

Introduction

It is a technique for searching a computer-stored document or database. The search engine examines all words in the record that might not perfectly match the search criteria in a fulltext search. The records consist of textual data like product descriptions, blog posts, articles, etc.

Types of full-text searches

Natural Language Fulltext Searches

Full-text searching interprets the search string as a free text using MATCH() and AGAINST functions. The MATCH() function specifies the column you want to search and explores a string against a text collection, and the AGAINST() function determines the search expression to be used.

Code

SELECTFROM table_name WHERE MATCH(col1, col2)
AGAINST('search terms' IN NATURAL LANGUAGE MODE)

Example

mysql> CREATE TABLE articles (
          id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
          title VARCHAR(150),
          body TEXT,
          FULLTEXT (title,body)
        ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.08 sec)

Boolean Fulltext Searches

A boolean search handles the search string using IN BOOLEAN MODE modifier in which the string contains the words to search for and certain characters have special meaning at the beginning or end of words in the search string. In this type of query, the + and - operators indicate that a word should be present or absent, respectively, for a match to occur. Thus, the query fetches all the rows with the word “Joins” but does not contain the word “right”.

Code

SELECT * FROM table_name WHERE MATCH(col1, col2)
AGAINST('search terms' IN BOOLEAN MODE);

Example

mysql> SELECTFROM tutorial WHERE  MATCH(title, description) AGAINST ('+Joins -right' IN BOOLEAN MODE);

Query Expansion Search Type

It is a natural language search type modification that performs the search twice. This is commonly used when a search phrase is too short, including a few most specific documents after performing the natural language search. 

Example

SELECTFROM posts   WHERE MATCH(title, descriptions)   
AGAINST('MySQL' WITH QUERY EXPANSION);  
Get the tech career you deserve, faster!
Connect with our expert counsellors to understand how to hack your way to success
User rating 4.7/5
1:1 doubt support
95% placement record
Akash Pal
Senior Software Engineer
326% Hike After Job Bootcamp
Himanshu Gusain
Programmer Analyst
32 LPA After Job Bootcamp
After Job
Bootcamp

Full-text index 

A full-text index is an index whose column data type is CHAR, VARCHAR, and TEXT. In MySQL, the full-text index is consistently named FULLTEXT.

It can be defined as the Fulltext index while creating the table using:

  • CREATE TABLE statement 
  • ALTER TABLE statement
  • CREATE INDEX statement 
     

CREATE TABLE statement
Here a new table named Company will be created. and it will have a FULLTEXT index that includes the article content column:

mysql> CREATE TABLE Company (  
    Id INT AUTO_INCREMENT NOT NULL PRIMARY KEY,  
    title VARCHAR(220),  
    body TEXT,  
    FULLTEXT (title, body)  
);  

 

ALTER TABLE Statement
The ALTER TABLE statement in MySQL is used to create a full-text index on the existing table.  

ALTER TABLE table_name    
ADD FULLTEXT (column1, column2,?)  

To create an index, We need to specify the table name in this syntax. Second, use the ADD FULLTEXT method to define the full-text index to one or more columns.

 

CREATE INDEX Statement
we have a table named company that contains columns department and project. Now, for these columns using the following statement, we can create a FULLTEXT index:

mysql> CREATE FULLTEXT INDEX company  
ON office (department, project)  


Removing FULLTEXT index

MySQL gives a command to remove the full-text index from the table. It can be done by using the ALTER TABLE DROP INDEX command.

Syntax

ALTER TABLE table_name DROP INDEX index_name;  

For example, executing the below statement will permanently remove the department  index from the "company" table

mysql> ALTER TABLE company DROP INDEX department;  

Advantages

  • Speed
    The full-text searching technique is fast as it examines the data based on complex search queries.
  • Support
    It has support for morphology.
  • Moderate Index Size
    The FULLTEXT index size is relatively small, and it contains stop words.
  • Fully Dynamic Index
    MySQL updates the index of that text column automatically whenever the data modification occurs in the column, and Results are ordered by relevance.

Disadvantages

  • Only InnoDB and MyISAM table are supported by Full text.
  • It does not support a partition table.
  • The MATCH( ) function parameters must be the identical column list from the table part of the FULLTEXT index definition, or on a MyISAM table, the MATCH( ) should be IN BOOLEAN MODE.
  • The '%' wildcard character for full-text searches is not used.
  • The argument to AGAINST( ) must be a constant string value when executing the query.
  • We can only create the full-text indexes for CHAR, VARCHAR, or TEXT columns.
  • The argument in AGAINST () must be an immutable string.

FAQs

  1. How do I create a full-text index in SQL?
    To create a full-text index, choose your table and right-click on that table, and select the "Define Full-Text Index" option. Now select Unique Index. The "Full-Text Index" table must have at least one unique index—select columns name and language types for columns.
     
  2. What is the advantage of full-text over-performing text search in MySQL?
    The full text allows much more complex searching, including And, Or, Not, even similar-sounding results (SOUNDEX) and many more items.
     
  3. How to drop a FULLTEXT index in MySQL?
    To drop a FULLTEXT index, you use the ALTER TABLE DROP INDEX statement.

Refer to know about :  Update Query in MySQL

Key Takeaways

In This article, we learned about full-text search in MySQL, its types, the idea of using the full-text index, and how to remove full-text index in MySQL, and at last, we discussed the advantages and disadvantages of full-text search.

Visit here to learn more about different topics related to database and management systems. Ninjas don't stop here. Check out the Top 100 SQL Problems to master frequently asked questions in big companies and land your dream job. Also, try  Coding Ninjas Studio to practice a wide range of DSA questions asked in many interviews.

Topics covered
1.
Introduction
2.
Types of full-text searches
2.1.
Natural Language Fulltext Searches
2.2.
Boolean Fulltext Searches
2.3.
Query Expansion Search Type
3.
Full-text index 
3.1.
Removing FULLTEXT index
3.2.
Advantages
3.3.
Disadvantages
4.
FAQs
5.
Key Takeaways