September 8, 2011 at 8:31 pm
I've run into a situation whereby my full-text index is much slower than a simple LIKE query against the same table. I suspect that either my data structure or my query is poor. I'm hoping for a new approach.
Some background:
1. The database is the backend for a CMS with multiple websites.
2. Rather than have a separate database for each website, the data for all websites is shared in the same tables (Pages, Content, etc.), with a clustered index on "ProjectID", the key that distinguishes content between websites.
3. The report I'm having trouble with is a "Site Search" report.
Basically, a vistor enters a search term, and pages with matching content is displayed. The full-text index is appropriate for this, as I can rank the results, and find matches that are similar but not identical to the original search term.
Here is a sample query that performs poorly (491,691 reads):
SELECT *
FROM CONTAINSTABLE( dbo.Content, *, @SearchTerms ) a
INNER JOIN
dbo.Content b ON a. = b.ContentID
WHERE b.ProjectID = @ProjectID
As compared to this one (2,605 reads):
SELECT *
FROM dbo.Content
WHERE ProjectID = @ProjectID
AND Content LIKE '%'+@SearchTerms+'%'
I'm sure that this is due to the clustered index on ProjectID, which reduces the amount of content being read. If there were some way to "Partition" the full-text index by ProjectID, I believe this would have a similar benefit. I know of no way of doing this.
As the Content table has grown, searching the full-text index has to traverse the entire collection of results before taking advantage of the index on ProjectID.
I am loathe to abandon the full-text index, as I would lose ranking of relevant results as well as the other options in a full-text predicate.
Does anyone have any suggestions as to the direction I can take this?
Jeffrey Kretz
September 9, 2011 at 10:36 am
In Sql Server 2005 FTS works on whole table, i.e. first all records are searched and then indexes applied
(another words, first FTS returns let say 1 M records and then clustered index can select only 1 from this 1M)-
there is no way around.
Sql Server 2008 has different behavior as FTS is part of Sql Server- FTS works on subset of data after all other indexes were applied.
September 9, 2011 at 6:42 pm
I was surprised to find out about this, as I am already using 2008 R2.
I fiddled around with a number of queries and as near as I could tell, the full-text query wasn't taking advantage of my index on ProjectID.
Then I tried creating a nonclustered index on ProjectID, with the pk of the table added as an included column. This finally resulted an improvement, cutting the execution time by 70%.
This has helped greatly, thanks.
Jeffrey Kretz
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply