What is WITH QUERY EXPANSION MODE in MySQL Fulltext Search

MATCH (col1,col2,...) AGAINST (expr [search_modifier])

search_modifier: 
{ 
     IN NATURAL LANGUAGE MODE 
   | IN NATURAL LANGUAGE MODE WITH QUERY EXPANSION    
   | IN BOOLEAN MODE 
   | WITH QUERY EXPANSION 
}

Could you please explain to me what QUERY EXPANSION is, and what does MySQL do with this mode?

The short answer to your question is that query expansion enhances the original search term by adding extra, relevant, information to the search term. Mis-quoting an example from Derek's link:


An example could be searching for books by Georges Simenon about
Maigret, when a user is not sure how to spell “Maigret”. A search for
“Megre and the reluctant witnesses” finds only “Maigret and the
Reluctant Witnesses” without query expansion. A search with query
expansion finds all books with the word “Maigret” on the second pass.

This tutorial should make things clearer:

Using 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

In some cases, users want to search for information that based on the knowledge that they have. Users use their knowledge to define keywords to search for information, and typically those keywords are too short. To help users to find what they want based on the too-short keywords, MySQL 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, MySQL full-text search engine looks for all rows that match

the search query.

• Second, it checks all rows in the search result and finds the

relevant words.

• Third, it performs a search again but based on the relevant words

instead the original keywords provided by the users.

From application perspective, you can use the query expansion when the search results are too few. You perform the searches again but with query expansion to offer user more information that are 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 take a look at an example to see how MySQL query expansion works.

We will use the productName column of the products table to demonstrate the query expansion feature. First, we enable the full-text search feature for this column.

ALTER TABLE products
ADD FULLTEXT(productName)

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

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

Gives

+-----------------------------------+
| productName                       |
+-----------------------------------+
| 1992 Ferrari 360 Spider red       |
| 1992 Porsche Cayenne Turbo Silver |
+-----------------------------------+
2 rows in set (0.00 sec)

As you see, the search results has 2 products whose product name contains 1992.

Third, we can widen the search result by using query expansion as the following statement:

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

Gives

+-------------------------------------+
| productName                         |
+-------------------------------------+
| 1992 Porsche Cayenne Turbo Silver   |
| 1992 Ferrari 360 Spider red         |
| 2001 Ferrari Enzo                   |
| 1932 Alfa Romeo 8C2300 Spider Sport |
| 1948 Porsche 356-A Roadster         |
| 1948 Porsche Type 356 Roadster      |
| 1956 Porsche 356A Coupe             |
+-------------------------------------+
7 rows in set (0.00 sec)

We got more rows in the search result when we used query expansion. The first two rows are the most relevant and the other rows come from the relevant keyword that are 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, we have introduced you to MySQL query expansion to widen the search results when the keywords provided by users are short.

Source: Using MySQL Query Expansion

StackExchange Database Administrators Q#15659, answer score: 6