MySQL Full-Text Search with Query Expansion

By /
Query Expansion

In MySQL, there are some limitations to the searching methods such as LIKE, boolean search and natural language search. These methods show the results of only particular keywords that users enter. Sometimes, these keywords are too short to search for particular information.

To overcome this problem, MySQL provides us with the full-text searching method called “Query Expansion”.

In this tutorial, we will learn what is query expansion and how to implement it with practical examples. Here we go!

Also read: MySQL Natural Language Full-Text Search

Introduction to Query Expansion

As explained earlier, query expansion overcomes the problem of searching the data based on only keywords that the user enters.

Based on automatic relevancy feedback, query expansion is utilized to broaden the search result of full-text searches (or blind query expansion). When the query expansion is utilized, the MySQL full-text search engine executes the following steps:

  • To start, look for all rows that match your search criteria.
  • Second, look for relevant terms in all of the search results’ rows.
  • Third, instead of using the original terms specified by users, do a new search using relevant phrases.

If we see the real-time example of query expansion, you can think of a google search. When you search for something, you get related results. At the bottom of the page, you can see related searches that may or may not consist of the keywords that you enter.

Now let’s see the syntax of query expansion full-text search.

Syntax of Query Expansion

The WITH QUERY EXPANSION search modifier in the AGAINST() function is used to use the query expansion. The syntax of the query utilising the WITH QUERY EXPANSION search modifier is shown below.

SELECT column1, column2
FROM table1
WHERE MATCH(target_cols) 
AGAINST('keyword',WITH QUERY EXPANSION);Code language: SQL (Structured Query Language) (sql)

Where,

  • Target_cols – Names of columns that you want to apply the search on.
  • keyword- Search term that you want to search.

Before Proceeding to Query Expansion

When particular indexes are used in MySQL, full-text searches are done, and such indexes have numerous unique characteristics, including the following:

  • The index must be of the FULLTEXT type in order to be classified as a full-text index.
  • Only tables using the InnoDB or MyISAM storage engines can use FULLTEXT indexes.
  • Only CHAR, VARCHAR, and TEXT columns can have FULLTEXT indexes.
  • When the MATCH() AGAINST() clause is used, FULLTEXT indexes are utilized

You can easily create a full-text index on a single column as well as on multiple columns. Here is the StackOverflow thread for creating a full-text index on a single column and multiple columns.

Examples of Query Expansion

For the demonstration of the full-text search method with query expansion, we will create a table and insert some data into it.

CREATE TABLE qeSearch(
id INT AUTO_INCREMENT PRIMARY KEY,
title VARCHAR(100),
body VARCHAR(100)
);Code language: SQL (Structured Query Language) (sql)
INSERT INTO qeSearch(title,body)
VALUES("mysql tutorial", "this is a mysql tutorial"),
("ml tutorial", "not a mysql tutorial"),
("android tut", "use firebase"),
("system programing", "8086 microprocessor"),
("datastructure & myDB", "mysql with DS"),
("advanced mysql", "data mining and graph database");Code language: SQL (Structured Query Language) (sql)

Let’s display the table data to check everything is inserted without an error.

SELECT * FROM qeSearch;Code language: SQL (Structured Query Language) (sql)
Qesearch Table Data
qesearch Table Data

Perfect! Now let’s write a simple query demonstrating the query expansion search method.

Before writing a query expansion statement, let’s search for the records by the keyword “tutorial”.

SELECT * FROM qeSearch
WHERE MATCH(title)
AGAINST('tutorial');Code language: SQL (Structured Query Language) (sql)

Here, we have applied the natural-language full-text search method to the title column. Let’s see what results we get.

Search Without Query Expansion
Search Without Query Expansion

As you can see, we have got two rows which contain the keyword “tutorial”. Now let’s use the query expansion and see how many records we get.

SELECT * FROM qeSearch
WHERE MATCH(title)
AGAINST('tutorial' WITH QUERY EXPANSION);Code language: SQL (Structured Query Language) (sql)
Query Expansion Example 1
Query Expansion Example 1

As you can see in the output, we have got three rows. The third row doesn’t contain the specified keyword but still, MySQL is showing us it in the output. That’s because MySQL finds the relevant keyword to the ‘tutorial’ and searches that relevant keyword again throughout the table.

Let’s take another example.

SELECT * FROM qeSearch
WHERE MATCH(title)
AGAINST('advanced' WITH QUERY EXPANSION);Code language: SQL (Structured Query Language) (sql)

Here, we are searching for the records by the keyword ‘advanced’.

Query Expansion Example 2
Query Expansion Example 2

If you look carefully, the first result we get is having id 6. The next result we get has id 1. This means, MySQL first searched for the exact keyword match, then it searched for records by relevant keywords.

To Remember Before Using Query Expansion

It’s worth noting that query expansion tends to increase noise by returning irrelevant results. It is strongly advised that you perform query expansion only when the searched keyword is short.

Conclusion

We learnt what query expansion is, how it works, and how to use it through practical examples in this tutorial. I hope you found this tutorial helpful in learning how to perform a full-text search using query expansion. We recommend that you use it in your projects for better results. Stay updated at mysqlcode.com for more interesting tutorials!

References

MySQL official documentation on query expansion.