FreeText COUNT query on multiple tables is super slow
- by Eric P
I have two tables:
**Product**
ID
Name
SKU
**Brand**
ID
Name
Product table has about 120K records
Brand table has 30K records
I need to find count of all the products with name and brand matching a specific keyword.
I use freetext 'contains' like this:
SELECT count(*)
FROM Product
inner join Brand
on Product.BrandID = Brand.ID
WHERE (contains(Product.Name, 'pants')
or
contains(Brand.Name, 'pants'))
This query takes about 17 secs.
I rebuilt the FreeText index before running this query.
If I only check for Product.Name. They query is less then 1 sec. Same, if I only check the Brand.Name. The issue occurs if I use OR condition.
If I switch query to use LIKE:
SELECT count(*)
FROM Product
inner join Brand
on Product.BrandID = Brand.ID
WHERE Product.Name LIKE '%pants%'
or
Brand.Name LIKE '%pants%'
It takes 1 secs.
I read on MSDN that: http://msdn.microsoft.com/en-us/library/ms187787.aspx
To search on multiple tables, use a
joined table in your FROM clause to
search on a result set that is the
product of two or more tables.
So I added an INNER JOINED table to FROM:
SELECT count(*)
FROM (select Product.Name ProductName, Product.SKU ProductSKU, Brand.Name as BrandName FROM Product
inner join Brand
on product.BrandID = Brand.ID) as TempTable
WHERE
contains(TempTable.ProductName, 'pants')
or
contains(TempTable.BrandName, 'pants')
This results in error:
Cannot use a CONTAINS or FREETEXT predicate on column 'ProductName' because it is not full-text indexed.
So the question is - why OR condition could be causing such as slow query?
