Table of contents
1.
Introduction
2.
Natural Language FULLTEXT Search
2.1.
Working of Natural language FULLTEXT search.
2.2.
Syntax
2.3.
Example
3.
FAQs
4.
Key takeaways
Last Updated: Mar 27, 2024

MySQL Natural Language FULLTEXT Search

Author Ankit Kumar
1 upvote
Career growth poll
Do you think IIT Guwahati certified course can help you in your career?

Introduction

Searching anything from a tremendous amount of data manually is next to impossible. MySQL server comes with a key feature known as FULLTEXT search to overcome this problem.

As the name suggests, FULLTEXT search searches the desired word or the character and displays the outcome as demanded by the user. Before executing a full-text search in the table, we must create a FULLTEXT index. The FULLTEXT can be applied to one or more character-type columns of a table.

We can only create the FULLTEXT index for VAR, VARCHAR, and TEXT type columns. By default, the search engine for the FULLTEXT search is InnoDB, but you can choose anyone in between InnoDB and MyISAM.

There are two methods to add the FULLTEXT search index in a table.

  1. At the time of creating the table.
  2. ALTER the table previously created and add the FULLTEXT index to the desired column.

There are three types of FULLTEXT search in the MySQL server.

  1. Natural language FULLTEXT search.
  2. Boolean FULLTEXT search.
  3. Query expansion FULLTEXT search.

The prime focus is to explain the Natural Language FULLTEXT search in detail.

Let us begin with the Natural language FULLTEXT search.

Natural Language FULLTEXT Search

Natural language full-text search interprets the search string as a free text (natural human language), and no special operators are required. By default, The FULLTEXT search uses a Natural language search unless the user activates another FULLTEXT search mode. You must be wondering how the Natural language FULLTEXT Search works. Let us answer this in the next section.

Working of Natural language FULLTEXT search.

During the natural language search, the MySQL server scans the rows or documents and finds the relevant documents matching the searched word and thus calculating the relevance score. Let us see what a relevance score is.

Relevance score is a positive floating-point number that denotes the similarity. If the relevance is found to be zero, that means there is no similarity between the searched word and the document. 

MySQL server computes the relevance with the help of many factors, including the number of words in the document, the number of unique words in the document, the total number of words in the collection, and the number of documents (rows) that contain a particular word.

Let us now see the syntax used by the natural language FULLTEXT search.

Syntax

The natural language search uses the following syntax.

SELECT * FROM table_name WHERE MATCH(col1,col2,...)

AGAINST(‘search terms’IN NATURAL LANGUAGE MODE);

As discussed, the FULLTEXT search uses the Natural language mode by default to omit the query 'IN NATURAL LANGUAGE MODE,' and it will work similarly.

After omitting, the syntax will look like the following.

SELECT * FROM table_name WHERE MATCH(col1,col2,...)

AGAINST(‘search terms’);

In the syntax, we first need to select all the columns of the table which we want to display; the MATCH () function specifies the names of the column where we want to perform the search operation, and the AGAINST() function contains the words to be searched by the user.

Let us now try to understand the natural language FULLTEXT search with the help of an example.

Example

Let us first create a table named Persons in the database named coding ninjas, which contains the first name and the address of the persons, and we will add the FULLTEXT index in one of the columns which have only characters.

The following query will create a table of our requirements.

use coding_ninjas;

CREATE TABLE Persons (

    PersonID int,

    FirstName varchar(255),

    Address varchar(255),

    fulltext(Address)

)Engine=Innodb;

Now, as our table is created, we will insert some values into it with the help of the following query.

INSERT INTO Persons VALUES  

('1', 'Ankit', 'Sainik school gopalganj,Bihar'),  

('2', 'Akshita', 'Nalanda, Bihar'),  

('3', 'Sumit','Mahua toli, Delhi'),  

('4', 'Amit', 'Anand vihar, New delhi')  

;  

Suppose we want to search the word 'Bihar' in our table, then it is evident that the word 'Bihar' will only be in the address column. So with the help of the following query of Natural Language FULLTEXT search, we can easily search for the word Bihar.

SELECT * FROM Persons WHERE MATCH(Address) 

AGAINST ('Bihar' IN NATURAL LANGUAGE MODE);

It will produce the following output.

As you can see, all the documents which contain the word Bihar are being displayed.

Now suppose we need to count the number of matches, then we can do it with the help of the following query.

SELECT COUNT(*) FROM Persons WHERE  MATCH(Address) 

AGAINST ('Bihar' IN NATURAL LANGUAGE MODE);

It will produce the following output.

We can always omit the mode specifier whenever we want to execute a  natural language FULLTEXT search as, by default, it searches in this mode only.

With this example, we made clear about the natural language full-text search. Let us now see some of the frequently asked questions related to the topic.

FAQs

  1. What is the minimum character limit of the word we can search for?
    The minimum word length should be four in MySQL natural language FULLTEXT search. We cannot search any word with less than four characters.
     
  2. Can MySQL search all the words?
    No! The MySQL search engine ignores the stop words. So one cannot search stop words from the document.
     
  3. Is it necessary to index the column with FULLTEXT before searching a word?
    Yes! You need to add the FULLTEXT index with the column you want to search.

Let us now summarise our learning in the next section.

Key takeaways

In this article, we discussed the search engine provided by the MySQL server. We saw different FULLTEXT searches and discussed the Natural language FULLTEXT search in detail. At first, we understood its working. It was followed by the syntax and then a suitable example. In the last section, we answered some of the frequently asked questions.
Ninjas do not stop here and explore Coding Ninjas Studio and practice Top 100 SQL problems handpicked by industry experts.

Live masterclass