I have a table with a full-text indexed column MiddlePart. The table has about 600,000 rows. The following query is very fast (30 results, <1 second):
select * from DomainName
where contains (MiddlePart, '"antiques*"')
OR freetext(MiddlePart, 'antiques')
This query is also very fast (5 results, <1 second):
select * from DomainName
where (contains (MiddlePart, '"dog*"') OR freetext(MiddlePart, 'dog'))
AND (contains (MiddlePart, '"training*"') OR freetext(MiddlePart, 'training'))
So why do these queries EXCEED more slowly? (90 seconds + and I canceled the request):
Request A:
select * from DomainName
where contains (MiddlePart, '"antiques*"')
OR freetext(MiddlePart, 'antiques')
union
select * from DomainName
where (contains (MiddlePart, '"dog*"') OR freetext(MiddlePart, 'dog'))
AND (contains (MiddlePart, '"training*"') OR freetext(MiddlePart, 'training'))
Request B:
select * from DomainName
where (contains (MiddlePart, '"antiques*"')
OR freetext(MiddlePart, 'antiques'))
OR
((contains (MiddlePart, '"dog*"') OR freetext(MiddlePart, 'dog'))
AND (contains (MiddlePart, '"training*"') OR freetext(MiddlePart, 'training')))
EDIT
Full text plan for QUERY A:
|--Merge Join(Union)
|--Nested Loops(Inner Join, OUTER REFERENCES:(FulltextMatch.[docid], [Expr1055]) WITH ORDERED PREFETCH)
| |--Stream Aggregate(GROUP BY:(FulltextMatch.[docid]))
| | |--Merge Join(Concatenation)
| | |--Table-valued function
| | |--Table-valued function
| |--Clustered Index Seek(OBJECT:([domaining].[dbo].[DomainName].[PK__DomainNa__3214EC2708EA5793]), SEEK:([domaining].[dbo].[DomainName].[ID]=FulltextMatch.[docid]) ORDERED FORWARD)
|--Merge Join(Left Semi Join, MERGE:([domaining].[dbo].[DomainName].[ID])=(FulltextMatch.[docid]), RESIDUAL:([domaining].[dbo].[DomainName].[ID]=FulltextMatch.[docid]))
|--Nested Loops(Inner Join, OUTER REFERENCES:(FulltextMatch.[docid], [Expr1056]) WITH ORDERED PREFETCH)
| |--Stream Aggregate(GROUP BY:(FulltextMatch.[docid]))
| | |--Merge Join(Concatenation)
| | |--Table-valued function
| | |--Table-valued function
| |--Clustered Index Seek(OBJECT:([domaining].[dbo].[DomainName].[PK__DomainNa__3214EC2708EA5793]), SEEK:([domaining].[dbo].[DomainName].[ID]=FulltextMatch.[docid]) ORDERED FORWARD)
|--Merge Join(Concatenation)
|--Table-valued function
|--Table-valued function
Full text plan for request B:
|
|
|
|
|
| |
| | |
| | |
| |
| |
| |
| |
|