FTI Query over Joined tables is slow

Question

Hi

I have a table with a full text index. I have a query which uses CONTAINS to search the table. This works well.

I now need to expand the query to also search in a parent table. The two tables are join with a primary/foreign key.

The parent table has a full text index and the columns I need to search are included in the index. The parent table is very small.

I added an Inner Join to the query and included the parent table search in an OR. For example:

select tb1.fields 

from tb1 inner join tb2 on tb1.id = tb2.id

where contains(tb1.field, 'searchvalue')

OR contains (tb2.field, 'searchvalue')

The problem is the search is now slow. What am I doing wrong?

Answer 1

OR is its self enable table scanning with Contains its impact performance greatly - you can achieve same thing by using union operator. Hope this helps you

select 

tb1.fields 

from tb1 
inner join tb2
on tb1.id = tb2.id

where contains(tb1.field, 'searchvalue')

union  

select 

tb1.fields 

from tb1 
inner join tb2
on tb1.id = tb2.id

where  contains (tb2.field, 'searchvalue')

---

http://uk.linkedin.com/in/ramjaddu