How CONTAINSTABLE works with many search words

Question

For example I got a table Companies. There is a field FullName in it, on which I got full-text index. Then I join that table with CONTAINSTABLE with @search_word like "company*" AND "name*" AND "oil*" AND "propan*" AND "liquid*"... from 1 to 10 words.

And I know that words (with variants *) got this number of matches:

• company - 10k
• name - 5k
• oil - 2k
• propan - 1k
• liquid - 500
• all words above in 1 row - 300 matches

So, if I will search in that order:

@search = '"company*" AND "name*" AND "oil*" AND "propan*" AND "liquid*"'

and in that order:

@search = '"liquid*" AND "propan*" AND "oil*" AND "name*" AND "company*"'

SELECT [FullName]
FROM dbo.Companies c
INNER JOIN CONTAINSTABLE (dbo.Companies, [FullName], @search) as s ON s.[KEY] = c.[KEY_FIELD];

Will I have any differences in speed of my query?

Answer 1

I ran a few tests monitoring "Query costs" of the Actual Execution Plan.

It seems overall cost of CONTAINSTABLE over any number of words joined in a search phrase with "AND" is equal to the cost of the least popular of those words alone.

Overall cost of CONTAINSTABLE over any number of words joined with "OR" is equal to the cost of the most popular of those words alone.

That suggests Full-Text Search engine prioritizes words from the search string in accordance with their popularity (occurrence count) in the index. Hence I think there would be no benefit in trying to pre-order search string words on the client.

Here are my Full-Text Search tests:

Declare @Word1        nvarchar(50) = N'"Word1*"';
Declare @Word2        nvarchar(50) = N'"Word2*"';
Declare @SearchString nvarchar(100) = '';

PRINT SUBSTRING(CONVERT(varchar, SYSDATETIME(), 121), 12, 11) + ' Start';
Set @SearchString = @Word1;
Select * From CONTAINSTABLE([Table], [Field], @SearchString);
PRINT SUBSTRING(CONVERT(varchar, SYSDATETIME(), 121), 12, 11) + ' ' + @SearchString;
Set @SearchString = @Word2;
Select * From CONTAINSTABLE([Table], [Field], @SearchString);
PRINT SUBSTRING(CONVERT(varchar, SYSDATETIME(), 121), 12, 11) + ' ' + @SearchString;
Set @SearchString = @Word1 + ' AND ' + @Word2;
Select * From CONTAINSTABLE([Table], [Field], @SearchString);
PRINT SUBSTRING(CONVERT(varchar, SYSDATETIME(), 121), 12, 11) + ' ' + @SearchString;
Set @SearchString = @Word2 + ' AND ' + @Word1;
Select * From CONTAINSTABLE([Table], [Field], @SearchString);
PRINT SUBSTRING(CONVERT(varchar, SYSDATETIME(), 121), 12, 11) + ' ' + @SearchString;
Set @SearchString = @Word1 + ' OR ' + @Word2;
Select * From CONTAINSTABLE([Table], [Field], @SearchString);
PRINT SUBSTRING(CONVERT(varchar, SYSDATETIME(), 121), 12, 11) + ' ' + @SearchString;
Set @SearchString = @Word2 + ' OR ' + @Word1;
Select * From CONTAINSTABLE([Table], [Field], @SearchString);
PRINT SUBSTRING(CONVERT(varchar, SYSDATETIME(), 121), 12, 11) + ' ' + @SearchString;

Please replace [Table], [Field] with your actual Full-Text-indexed table and field names, and set @Word1 and @Word2 to popular and rear words from your data set.