How CONTAINSTABLE works with many search words

Question

For example, I got a table Companies . It has a FullName field on which I received a full-text index. Then I join this table using CONTAINSTABLE with @search_word as "company*" AND "name*" AND "oil*" AND "propan*" AND "liquid*"... from 1 to 10 words.

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

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

So, if I search in this order:

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

and in the following 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 the speed of my request?

Answer 1

I have tested several “Cost of inquiry” monitoring tests in terms of actual execution.

It appears that the total cost of CONTAINSTABLE over any number of words associated with "AND" in the search phrase is equal to the value of the smallest popular only of these words.

The total cost of CONTAINSTABLE over any number of words connected with "OR" is equal to the cost of the most popular only for these words.

This suggests that the full-text search engine prioritizes the words from the search string according to their popularity (number of occurrences) in the index. Therefore, I think that trying to pre-arrange the search strings on the client will not be of any use.

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 the actual tables and field names marked with full text, and set @Word1 and @Word2 in the popular and back words from your dataset.