MySQL Query Expansion FULLTEXT Search

Introduction

Searching for specific data from a large amount of data. Sounds familiar? As the saying goes searching for a needle in a haystack. To address this issue, the MySQL server includes a function called FULLTEXT search. 

FULLTEXT search, the name defines the work. It searches for the specific word or character that we want to search. It brings the searched word/data that the user wants. And yeah, it works like a charm. Wait, before we jump and search for the full text, we need to follow a rule, and that is to make a FULLTEXT index. A table's FULLTEXT function can be used for one or more character-type columns. There are some constraints too that the FULLTEXT index can only be created for VAR, VARCHAR, and TEXT type columns. InnoDB is the default search engine for the FULLTEXT search, although you may pick between InnoDB and MyISAM. 

The FULLTEXT search may be added in two ways.

1. When the Table is created. 
2. ALTER the Table you just made and add the FULLTEXT index to the column you want.

In the MySQL server, there are three types of FULLTEXT searches.

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

Here, we are going to learn about the Query expansion FULLTEXT search.

Query Expansion FULLTEXT Search

There are instances when the user desires to look for a specific result or information based on prior knowledge. The user wants to use some specific keyword to look for that information, but generally, those keywords are quite short. But wait here, MySQL is there to cater to the needs. Query expansion is the answer to the users' blues. 

Working of Query Expansion FULLTEXT Search

Since the full-text search returns a concise result whereas query expansion is used to increase the result by using automatic relevance feedback (or blind query expansion)

When the query expansion is utilized, the MySQL full-text search engine executes the following steps:

1. To begin, the MySQL full-text search engine searches for all rows that match the search query. 
2. Second, it searches all of the rows in the search result for relevant terms. 
3. Third, it does another search, using related terms rather than the consumers' initial keywords.

Syntax

Using the query expansion, the user uses the WITH QUERY EXPANSION search modifier in the AGAINST() function. The syntax that is to be used is as follows:

When the number of search results is insufficient, the user can utilize the query expansion feature in the program. The user may do the searches again, but this time using query expansion will provide more related and relevant information to what they are looking for.

Now let us move forward and look at an example.

Example

Let's start by making a table called Cars in the coding_ninjas database. The Table contains the car name and ID and then adds the FULLTEXT index to one of the columns containing only characters. 

The query below will generate a table with all of our criteria.

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

Without utilizing query expansion, we look for cars whose names contain the '1992' keyword.

This query will produce the following output.

Now, to widen the result, we can use the following command.

Now, this will be the output.

Well, it's worth noting that query expansion tends to add a lot of noise by delivering irrelevant results. When the searched keyword is short, it is strongly advised that you employ query expansion.