I have a table with a full-text indexed column MiddlePart
. The table has around 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 are BOTH these queries EXCEEDINGLY slow? (90seconds+ and I cancelled the query):
Query 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'))
Query 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 Query B:
|--Nested Loops(Left Semi Join, OUTER REFERENCES:([domaining].[dbo].[DomainName].[ID]))
|--Clustered Index Scan(OBJECT:([domaining].[dbo].[DomainName].[PK__DomainNa__3214EC2708EA5793]))
|--Concatenation
|--Table-valued function
|--Nested Loops(Left Semi Join)
| |--Concatenation
| | |--Table-valued function
| | |--Table-valued function
| |--Row Count Spool
| |--Concatenation
| |--Table-valued function
| |--Table-valued function
|--Table-valued function