I have two tables in a SQL Server database with basic fulltext indexes:
CREATE FULLTEXT CATALOG table1_catalog;
CREATE FULLTEXT INDEX ON table1
(
name1
)
KEY INDEX pk_table1
ON table1_catalog;
ALTER FULLTEXT INDEX ON table1 START UPDATE POPULATION;
CREATE FULLTEXT CATALOG table2_catalog;
CREATE FULLTEXT INDEX ON table2
(
name2
)
KEY INDEX pk_table2
ON table2_catalog;
ALTER FULLTEXT INDEX ON table2 START UPDATE POPULATION;
The tables have approximately 100,000 entries.
The following two queries return immediately when there are no results:
SELECT * FROM table1 t1
LEFT JOIN table2 t2 on t1.id=t2.id
WHERE CONTAINS(t1.*, '"noresult"');
-- Result in 00:00:00
SELECT * FROM table1 t1
LEFT JOIN table2 t2 on t1.id=t2.id
WHERE CONTAINS(t2.*, '"noresult"');
-- Result in ~50ms
But the following takes much longer:
SELECT * FROM table1 t1
LEFT JOIN table2 t2 on t1.id=t2.id
WHERE CONTAINS(t1.*, '"noresult"');
OR CONTAINS(t2.*, '"noresult"');
-- Result in ~22,000ms
The queryplans are here: http://pastebin.com/SBkgpF8X
Is there any explanation for this? Can I fix it or do I need to perform to separate queries.
