Performance problem
MyTable:
RowNum INT (IDENTITY)
PageTitleNVARCHAR(MAX)
DomainNameVARCHAR(1000)
Indexes:
Primary key, clustered onRowNum
Full Text index onPageTitle,
Index onDomainName
Data patterns: hundreds or thousands rows per each DomainName value
When user performs search, the task is to present 1 entry per DomainName (despite the fact that hundreds of pages on the same site can contain the word). 10 entries per page are presented. Now it is implemented in the following way (using application):
1-st result:
SELECT top 1 *
FROM MyTable
WHERE CONTAINS(PageTitle, '"word"')
Let's say, 1st Domain was www.cnn.com
2-nd result:
SELECT top 1 *
FROMMyTable
WHERE CONTAINS(PageTitle, '"word"')
ANDDamainNameNOT IN ('www.cnn.com')
First pages work fine. The problem starts somewhere on 6th page. Execution plan - nested loop between primary key index seek and full text index. Afterwards seek for domain name. But since we have thousands of entries per domain, when we have ~60 domain names in the NOT IN (...), the query starts to take more time. And take into account that there're 10 such a queries per page. Hint that makes the query to use index on DomainName, doesn't improve performance. New non-clustered index on (RowNum, DomainName) doesn't help - Optimizer doesn't use it since it needs clustered index for join with full-text index.
The only thing I can think of - is to make primary key non-clustered while create new unique clustered index on (RowNum, DomainName). But it may influence full-text indexes. Can you think of any other solution? And what do you think about mine?
