Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
Looks like the problems is not specific to using two full-text searches. The same problem exists with a single full-text search combined with another simple search. I have asked a related question here: stackoverflow.com/questions/16438556/… –  Rasmus Faber May 8 '13 at 10:43
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.