MySQL Query Expansion

Summary: in this tutorial, you will learn about MySQL query expansion to widen the search results based on automatic relevance feedback.

Introduction to MySQL Query Expansion

Typically, users search for information based on their knowledge. They use their experience to come up with keywords to search for information, and sometimes, these keywords are too short.

To help users to find information based on these too-short keywords, MySQL's full-text search engine introduces a concept called query expansion.

The query expansion is used to widen the search result of the full-text searches based on automatic relevance feedback (or blind query expansion). Technically, MySQL full-text search engine performs the following steps when the query expansion is used:

• First, search for all rows that match the search query.
• Second, find the relevant words in all rows from the search result.
• Third, search again based on the relevant words instead of the original keywords specified by users.

From the application perspective, you can use the query expansion when the search results are too few. You perform the searches again, but with the query expansion, to offer users more information related and relevant to what they are looking for.

To use the query expansion, you use the WITH QUERY EXPANSION search modifier in the AGAINST() function. The following illustrates the syntax of the query using the WITH QUERY EXPANSION search modifier.

SELECT column1, column2
FROM table1
WHERE MATCH(column1,column2) 
      AGAINST('keyword',WITH QUERY EXPANSION);

MySQL query expansion example

Let’s look at an example of using a query expansion to see how it works.

We will use the productName column of the products table to demonstrate the query expansion feature.

First, create the full-text search index on the productName column:

ALTER TABLE products 
ADD FULLTEXT(productName);

Second, search for a product whose name contains the 1992  without using query expansion.

SELECT 
    productName
FROM
    products
WHERE
    MATCH (productName) 
    AGAINST ('1992' );

As you see clearly from the output, the search result has two products whose names contain the term 1992 .

Third,  use query expansion to widen the search result:

SELECT 
	productName 
FROM 
	products 
WHERE 
	MATCH(productName) 
    AGAINST('1992' WITH QUERY EXPANSION);

The result set has two more rows when used with the query expansion. The first two rows are the most relevant and the other rows come from the relevant keyword derived from in the first two rows, e.g.,Ferrari

Notice that blind query expansion tends to increase noise significantly by returning non-relevant results. It is highly recommended that you use query expansion only when the searched keyword is short.

In this tutorial, you have learned how to use MySQL query expansion to widen the search results when the search query is too short.