What is a full-text search in MySQL?

The MySQL server lets users run full-text queries against character-based data in MySQL tables. We can use it to power search results on websites like shops, search engines, newspapers, and more. More specifically, FTS retrieves documents that don't match the search criteria perfectly. It would help to create a full-text index on the table before running full-text queries on a table. The full-text index can contain one or more character-based columns in the table.

What is the importance of MySQL full-text search?

The MySQL full-text search capability provides a simple way to implement various search techniques (natural language search, query expansion search, and boolean search) into your application running MySQL.

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. It is compulsory for the "Full-Text Index" table must have at least one unique index—select columns name and language types for columns.

Table of contents

Introduction

Relevance Score

MySQL Boolean full-text search operators

Features

FAQs

Key Takeaways

Last Updated: Mar 27, 2024

MySQL Boolean FULL-TEXT Search

Author Shivani Kumari

Do you think IIT Guwahati certified course can help you in your career?

Yes

Introduction

MySQL has an additional form of full-text search called Boolean full-text search. It can perform boolean full-text searches using the IN BOOLEAN MODE modifier. We use the IN BOOLEAN MODE modifier in the AGAINST expression to perform a full-text search in the Boolean mode.

Example

In this example, we will search for a product whose product name contains the Truck word.

SELECT productName, productline
FROM products
WHERE MATCH(productName) 
      AGAINST('Truck' IN BOOLEAN MODE );

Output

Only those products are returned whose product name contains the Truck word.

With IN BOOLEAN MODE modifier, certain characters have special meaning at the beginning or end of words in the search string. For example, the + and - operators indicate that a word must be present or absent, respectively, for a match to occur.

Example

In this example, we search for the products whose product names contain the Truck word but do not have the Pickup word. We will use the exclude Boolean operator (-), which returns the desired result:

SELECT productName, productline
FROM products
WHERE MATCH(productName) AGAINST('Truck -Pickup' IN BOOLEAN MODE );

Output

Relevance Score

The MATCH() function provides relevance score for every row in the table. Rows will be shown in such a way that rows with higher relevance will come first. This value decides how it is relevant to the search item. The relevance score is a non-negative floating-point number.

Example

SELECT title, MATCH (title, descriptions)   
AGAINST ('Java, Workbench' IN BOOLEAN MODE) AS relevance_score   
FROM Posts WHERE MATCH (title, descriptions)   
AGAINST ('Java, Workbench' IN BOOLEAN MODE);

Output

MySQL Boolean full-text search operators

This table lists the operators used in the full-text Boolean search mode and their meanings:

Operator	Description
+	It represents that the word must be present in each returned row.
-	It represents that the word must not be present in each returned row.
>	It increases the relevance value.
<	It decreases the relevance value.
()	It is used to group words into subexpressions (allowing them to be included, excluded, ranked, and so forth as a group).
~	It negates the ranking value of a word.
*	It represents a wildcard at the end of the word.
“”	It defines a phrase (as opposed to a list of individual words, the entire phrase is matched for inclusion or exclusion).

Examples

Search Query	Expression
Search for rows with at least one of the two words: Dbms or tutorial.	'Dbms tutorial'
Search rows that contain both words: Dbms and tutorial	'+Dbms +tutorial'
Search rows containing the word "Dbms" and put the higher rank for the rows with "tutorial."	'+Dbms tutorial'
Search rows that contain the word "Dbms" but not "tutorial."	'+Dbms -tutorial'
Search for rows containing the word "Dbms" and rank the row lower if it includes the word "tutorial."	'+Dbms ~tutorial'
Search for rows that have the words "Dbms" and "tutorial," or "Dbms" and "training" in whatever order, but put the rows that contain the term "Dbms tutorial" higher than "Dbms training."	'+Dbms +(>tutorial <training)’
Search rows that contain words starting with "my" such as "mySQL," "mydatabase," etc.,	'my*'

Features

MySQL does not sort rows by relevance in descending order in Boolean full-text search by default.
InnoDB tables require all columns of the MATCH expression have a FULLTEXT index to perform Boolean queries. Notice that MyISAM tables do not need this, although the search is relatively slow.
MySQL avoids multiple Boolean operators on a search query on the InnoDB tables, e.g., '++apple.' MySQL will return an error if we do so. However, MyISAM behaves differently in such cases. It ignores other operators and uses the operator closest to the search word. For example, '+-apple' will become '-apple.'
InnoDB's full-text search does not support the trailing plus (+) or minus (-) sign. It only supports the leading plus or minus sign. MySQL will give an error if you search word is 'applel+' or 'apple-.' In addition, the following leading plus or minus with wildcard are invalid: +*, +-
The 50% threshold means if a word appears in more than 50% of the rows, MySQL will ignore it in the search result.

FAQs

What is a full-text search in MySQL?
The MySQL server lets users run full-text queries against character-based data in MySQL tables. We can use it to power search results on websites like shops, search engines, newspapers, and more. More specifically, FTS retrieves documents that don't match the search criteria perfectly. It would help to create a full-text index on the table before running full-text queries on a table. The full-text index can contain one or more character-based columns in the table.
What is the importance of MySQL full-text search?
The MySQL full-text search capability provides a simple way to implement various search techniques (natural language search, query expansion search, and boolean search) into your application running MySQL.
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. It is compulsory for the "Full-Text Index" table must have at least one unique index—select columns name and language types for columns.

Key Takeaways

In this article, we extensively discussed MYSQL Boolean Full-Text search We have also seen various examples of search expressions using different search operators. We know about relevance scores and additional features provided by the MYSQL Boolean Full-Text search.

We hope that this blog has helped you to enhance your knowledge regarding MYSQL Boolean Full-Text search. Check out articles on link Coding Ninjas Studio.Do upvote your blogs to help other ninjas grow. Happy Coding!!

Live masterclass

Amazon PowerBI & AI Essentials: Data Visualization Tips

by Abhishek Soni

15 May, 2025

01:30 PM

311+ registered

Amazon SDE Resume Tips: Get Noticed, Get Hired

by Shantanu Shubham

12 May, 2025

01:30 PM

948+ registered

Microsoft Data Analytics Interview: How to Clear It?

by Prerita Agarwal

13 May, 2025

01:30 PM

518+ registered

Ace Data Interviews: Master Analytics with AI Tools

by DP Aurosish

17 May, 2025

07:30 AM

9+ registered

Crack Google SDE Interviews: Strategies to Master DSA

by Saurav Prateek

14 May, 2025

01:30 PM

193+ registered

Amazon PowerBI & AI Essentials: Data Visualization Tips

by Abhishek Soni

15 May, 2025

01:30 PM

311+ registered

Amazon SDE Resume Tips: Get Noticed, Get Hired

by Shantanu Shubham

12 May, 2025

01:30 PM

948+ registered

View more events