When creating a search functionality in the application or website, you might have used the LIKE statement to search for a particular result.
However, it’s not always the best option to find particular records by using the entered text. What if a user enters two words in the search box which brings no result, but individual keywords of the search term might bring few results? In that case, a full-text search is the best option.
In this tutorial, we will see what is a full-text search, what are its types, and the pros-cons of each type. Here we go!
What is a Full-text Search?
Full-text search refers to searching specific text among large amounts of electronic text data and delivering results that include some or all of the words in the query. Traditional searches, on the other hand, would give exact matches.
Let’s take an example. We will see a scenario in the traditional search and then a full-text search.
Why use Full-text Search?
Suppose you search the keyword “MySQL”. Using the LIKE operator, it will return all records which contain the word “MySQL”. You can write a simple query to search that particular word in a particular column. However, you can write a complex query to search that word in all columns of the table.
But what if the user searches for “MySQL database” and there is no record that consists of the term “MySQL database”. In that case, you will get nothing; however, there might be records that consist of the keyword “MySQL” or “database” separately.
An ideal application is that which returns some related results if the exact keywords don’t match.
Using a full-text search, you can get a result which is an exact match to the searched keywords, related matches as well as individual keyword matches of the searched term.
Before You Proceed to Full-text Search
Before you learn the types of full-text search, you must know the following terms.
- A full-text is an index of type FULLTEXT.
- The index must be of the FULLTEXT type in order to be classified as a full-text index.
- Only tables using the InnoDB or MyISAM storage engines can use FULLTEXT indexes.
- Only CHAR, VARCHAR, and TEXT columns can have FULLTEXT indexes.
- When the MATCH() AGAINST() clause is used, FULLTEXT indexes are utilized
The MATCH() AGAINST() syntax is used to do full-text searches. MATCH() takes a comma-separated list of column names to search. AGAINST takes a search string and an optional modifier that specifies the type of search to be performed. The search string must have a constant string value throughout the query evaluation.
A quick note- Loading data into a database with no FULLTEXT index and then creating the index is much faster for big data sets than loading data into a table with an existing FULLTEXT index.
How to Create FULLTEXT Index
You can easily create a full-text index on a single column as well as on multiple columns. Here is the StackOverflow thread for creating a full-text index on a single column and multiple columns.
Types of Full-Text Search
There are three types of full-text search-
- Natural language Full-text search
- Boolean Full-text search
- Query Expansion Full-text Search
Natural Language Full-Text Search
Natural language search is one of the full-text search techniques in MySQL that returns results that are related to natural language and it focuses on natural human language queries.
Relevance is a positive integer having a decimal value of one. There is no similarity when the relevance is 0. When MySQL determines relevance, it examines 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 include a certain word.
For a detailed tutorial on natural-language full-text search, click here. We have covered everything about natural-language full-text search along with easy and effective examples in the tutorial.
Boolean Full-text search
In contrast to natural language full-text searches, which use concepts, boolean full-text searches use words. It allows us to search using incredibly sophisticated queries that include Boolean operators like less than () and more than (>), plus (+) and minus (-) signs, subexpressions (“(” and “)”), double quotes (“”), an operator that minimises the value’s contribution to the results (), and wildcards.
For a detailed tutorial on Boolean full-text search, click here. We have covered everything about Boolean full-text search along with easy and effective examples in the tutorial.
Query Expansion Full-text Search
Query expansion is used to extend the search result of full-text searches based on automated relevance feedback (or blind query expansion). The MySQL full-text search engine performs the following stages when query expansion is used:
- To begin, check for all rows that correspond to your search parameters.
- Second, scan all of the rows of search results for relevant phrases.
- Third, instead of utilising the terms that consumers originally chose, do a fresh search using relevant phrases.
For a detailed tutorial on query expansion full-text search, click here. We have covered everything about query expansion full-text search along with easy and effective examples in the tutorial.
Conclusion
It’s worth mentioning that query extension tends to add to the amount of noise by delivering irrelevant results. It is strongly recommended that you use query expansion when the searched keyword is short. It is a great feature of MySQL that you can implement in your application to broaden search results.
References
MySQL official documentation on full-text search.