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 the users find information based on these 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 using the query expansion:

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);Code language: SQL (Structured Query Language) (sql)

MySQL query expansion example

First, create a table called documents with a FULLTEXT index that includes the contents column

DROP TABLE IF EXISTS documents;

CREATE TABLE documents (
    id INT AUTO_INCREMENT PRIMARY KEY,
    contents TEXT,
    FULLTEXT ( contents )
);

INSERT INTO documents(contents)
VALUES
('MySQL Database'),
('MySQL'),
('Database'),
('SQL'),
('A fork of MySQL'),
('SQLite');Code language: SQL (Structured Query Language) (sql)

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

SELECT 
  id, 
  contents 
FROM 
  documents 
WHERE 
  MATCH (contents) AGAINST ('MySQL');Code language: SQL (Structured Query Language) (sql)

Output:

+----+-----------------+
| id | contents        |
+----+-----------------+
|  1 | MySQL Database  |
|  2 | MySQL           |
|  5 | A fork of MySQL |
+----+-----------------+
3 rows in set (0.00 sec)Code language: JavaScript (javascript)

Third, use query expansion to widen the search result:

SELECT 
  id, 
  contents
FROM 
  documents 
WHERE 
  MATCH (contents) 
  AGAINST ('MySQL' WITH QUERY EXPANSION)Code language: SQL (Structured Query Language) (sql)

Output:

+----+-----------------+
| id | contents        |
+----+-----------------+
|  5 | A fork of MySQL |
|  1 | MySQL Database  |
|  3 | Database        |
|  2 | MySQL           |
+----+-----------------+
4 rows in set (0.00 sec)Code language: JavaScript (javascript)

The result set has two more rows when used with the query expansion. The third row comes from the relevant keyword derived from other rows that contain the word “MySQL”.

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.

Summary

Use MySQL query expansion to widen the search results when the search query is too short.

Was this tutorial helpful?