Fulltext search using query expansion in MS-SQL

Question

I am using fulltext search and using query expansion mode in MySQL.

Query expansion works like this: the most relevant words found with your initial search term are added to your initial search term and the final search is performed. The query returns a broader result with your search term and what might be interesting

SELECT *
FROM  table_name
WHERE MATCH(title,description,vendor) AGAINST ('1992' WITH QUERY EXPANSION)

and the output that I am getting is exactly what I need.

and when I use

SELECT *
FROM table_name
WHERE MATCH(title,description,vendor) AGAINST ('1992' IN NATURAL LANGUAGE mode)

It brings just first 2 rows which is fine and equivalent to the output of MS-SQL server with this query

SELECT * FROM s2hubapplication_application
WHERE CONTAINS([description], '1992')

What I want to know is, do we have any similar mode like WITH QUERY EXPANSION in MS-SQL?

Answer 1

I believe the short answer is no, the query expansion component of FullText matching is not available by default in MSSQL Server. I am not in the know of engine level operations in MySQL to make this viable performance wise (or if there are any further optimisations).

Taking the explanations from: MySql Doc, and Dba Question. What it does is:

• 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.

However you can make SQL Server do the same, just need to write it your self (and you can make it in a multitude of ways). A quick set up:

CREATE TABLE [dbo].[FullTextExmaple] (
    [FT_ID] [int] NOT NULL,
    [FT_Title] [Varchar] (255),
    [FT_Vendor] [Varchar] (255) ,
    [FT_Description] [Varchar] (500)
    CONSTRAINT [FT_TEST_PK] PRIMARY KEY  CLUSTERED ([FT_ID])
)

INSERT INTO [dbo].[FullTextExample]([FT_ID],[FT_Title],[FT_Vendor],[FT_Description])
     VALUES
          (1,'Test Application 1', '','1992 Porsche Cayenne'),
          (2,'Test Application 2', '','1992 Porsche Cayenne'),
          (3,'Test Application 3', '','1992 Ferrari Spider'),
          (4,'Test Application 4', '','2001 Ferrari Enzo'),
          (5,'Test Application 5', '','1995 Alfa Romeo Spider'),
          (6,'Test Application 6', '','1956 Porsche Coupe'),
          (7,'Test Application 7', '',''),
          (8,'Test Application 8', '','1980 Jaguar')

Above is just a quick sample set of data.

SELECT DISTINCT FT_Description FROM [dbo].[FullTextExample]
WHERE CONTAINS([FT_Description], 'FORMSOF(INFLECTIONAL, Spiders)') --Note the plural
--Uses MSSQL FullText search, allowing you to use i.e. INFLECTIONAL to hit variants/synonyms
-- FT_Description
-- 1992 Ferrari Spider
-- 1995 Alfa Romeo Spider

For this example I only want to use the other Description words. Mimicing the second step of MySQL functionality.

WITH Search_Results(ResultDescriptions)
AS
(
    SELECT DISTINCT FT_Description FROM [dbo].[FullTextExample]
    WHERE CONTAINS([FT_Description], 'FORMSOF(INFLECTIONAL, Spiders)')
)
SELECT DISTINCT KeyWords.value AS [KeyWord] from Search_Results 
CROSS APPLY string_split(ResultDescriptions, ' ') AS KeyWords
-- KeyWord
-- 1992
-- 1995
-- Alfa
-- Ferrari
-- Romeo
-- Spider

By splitting each of the distinct words, we can run a new search based on these values. Mimicing the third step.

WITH Search_Results(ResultDescriptions)
AS
(
    SELECT DISTINCT FT_Description FROM [dbo].[FullTextExample]
    WHERE CONTAINS([FT_Description], 'FORMSOF(INFLECTIONAL, Spiders)')
)
SELECT DISTINCT FT_ID, FT_Title, FT_Vendor, FT_Description FROM dbo.FullTextExample AS FT
INNER JOIN
(SELECT DISTINCT KeyWords.value AS [KeyWord] from Search_Results 
CROSS APPLY string_split(ResultDescriptions, ' ') AS KeyWords) AS KeyWords
--Adding spaces below to pick up 'words' at start and end
ON PATINDEX('% ' + KeyWord + ' %', ' ' + FT_Description + ' ') > 0
-- Returned Rows:
-- 1, Test Application 1, , 1992 Porsche Cayenne
-- 2, Test Application 2, , 1992 Porsche Cayenne
-- 3, Test Application 3, , 1992 Ferrari Spider
-- 4, Test Application 4, , 2001 Ferrari Enzo
-- 5, Test Application 5, , 1995 Alfa Romeo Spider

You can expand this, and this is a method without using a function/stored procedure. Other ways are to build your last query through dynamic SQL and a loop, allowing you to use CONTAINS/FREETEXT again.

This is an illustration of how it can be done. The initial 'keyword' filtering can also be expanded upon by making use of the entire toolkit of Full Text searching (and indexing). Msdn Link