MySQL Query Expansion FULLTEXT Search

We know that every user searches for information based on their knowledge. They use some keywords to search for information, and those keywords are too short. We can solve this problem and help the user to find what they want using a new concept introduced by MySQL known as query expansion.

The query expansion in MySQL is used to widen the search result of the full-text searches based on automatic relevance feedback or blind query expansion. When the query expansion is used, MySQL full-text search engine performs the below steps:

Step 1: It will first search for all the rows that match the given search query.

Step 2: It will find all the relevant words by checking all rows in the search.

Step 3: Finally, it will search again based on these relevant words instead of the original keywords specified by the users.

After understanding the concept of query expansion, it might be clear that we can use the query expansion when the search results are too short. We can perform the searches again, but users get more information relevant to what they are looking for with this concept.

If we want to use the query expansion full-text search, we need to add the WITH QUERY EXPANSION search modifier in the AGAINST() function. In other words, it is enabled by adding WITH QUERY EXPANSION or IN NATURAL LANGUAGE MODE WITH QUERY EXPANSION following the search phrase. The below is the basic syntax that illustrates this search in MySQL:

Let us understand how a query expansion full-text search works in MySQL through example.

MySQL Query Expansion Example

Suppose we have a table named posts that contain the below data. This table has a title and description column as the full-text index.

The following example displays the search result for a post's information whose name contains the 'MySQL' term without using query expansion:

Executing the query will return the three post name that contains the 'MySQL' term:

Now, we will use the query expansion to widen the search as below:

Executing the statement, we will get one more row in the result set when using the query expansion. Here the first three rows are the most relevant, and the other rows come from the relevant keyword derived from the first three rows.

It is to note that blind query expansion increases noise significantly by returning non-relevant outputs, so it is recommended to use it only when a searched phrase is short.