login

Subscribe to MySQL Tutorial websiteRSS Feed
 » MySQL Full-Text Search » Using MySQL Query Expansion

Using MySQL Query Expansion

Earn extra cash at home! Thousands of MySQL related projects are waiting for you to finish.

In some cases, users want to search for information which they relied on the knowledge they have in their mind. Then they use that knowledge to define keywords to search and normally those keywords are too short. To overcome this problem, MySQL full-text search engine introduce a so-called concept which is known as query expansion

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

  • First MySQL full-text search engine looks for all rows that match the search query
  • Next MySQL full-text search checks all returned rows and find the relevant words.
  • Finally MySQL full-text search engine performs search again but this time based on the relevant words.

From application perspective, you use the query expansion when the returned search results are a few . You perform search again but with query expansion to offer user more information related to what they are looking for.
To use the query expansion, you use modifier WITH QUERY EXPANSION in SELECT statement. Here is the typical form of using WITH QUERY EXPANSION:

SELECT column1, column2
FROM table1
WHERE MATCH(column1,column2) 
      AGAINST(‘keyword’,WITH QUERY EXPANSION)

Let’s take a look at an example to see how query expansion works.

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

ALTER TABLE products 
ADD FULLTEXT(productName)

We find all products which product name has word ‘1992’ without using query expansion.

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

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

As you see, the product name contains 1992. Now we can widen the returned result by using query expansion:

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

+-------------------------------------+
| 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)

As you see we have more returned rows when using query expansion. First two rows are the most relevant result. The rest of remaining rows come from the relevant words from the first two rows such as ‘Ferrari’.

Note that blind query expansion tends to increase noise significantly by returning non-relevant results. It is highly recommended to use only when a keyword (or search phrase) is rather short.

Search