May 21, 2012 at 8:18 am
Hello!
I have a table
create table FY_Objects
(
ID bigint identity primary key,
CategoryID bigint,
Title nvarchar(240)
);
create index ix_ttt_id_cat
on FY_Objects(ID, CategoryID)
I have Fultext index on Title column.
The following query
select CategoryID, COUNT(*)
from FY_Objects with(nolock)
where contains(*, 'example')
group by CategoryID
order by COUNT(*) desc;
takes about 300ms on 13 million rows (all rows loaded in cache, ~100,000 rows contain 'example').
Query plan is attached to the message. I see that SQL Server goes to another index for each row that was found in Fulltext index.
Is it possible to include somehow CategoryID into the FullText Index as I do for regular indexes? Or are there other ways to speed-up my query?
thanks in advance.
Anton Burtsev
May 21, 2012 at 8:52 am
That may help:
It's still applicable to 2008.
May 23, 2012 at 2:05 am
Eugene Elutin (5/21/2012)
That may help:It's still applicable to 2008.
Eugene,
This helps in search scenario, not grouping.
Do you know any other fulltext solution that allow fast grouping?
Anton Burtsev
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply