Search in multiple tables with Full-Text

I'm trying to make a detailed search with asp and SQL Server Full-text.

When a keyword submitted, I need to search in multiple tables. For example,

Table - Members

• member_id
• contact_name

Table - Educa开发者_开发技巧tion

• member_id
• school_name

My query;

select mem.member_id, mem.contact_name, edu.member_id, edu.school_name from Members mem FULL OUTER JOIN Education edu on edu.member_id=mem.member_id where CONTAINS (mem.contact_name, '""*"&keyword&"*""') or CONTAINS (edu.school_name, '""*"&keyword&"*""') order by mem.member_id desc;

This query works but it takes really long time to execute.

Image that the keyword is Phill; If mem.contact_name matches then list it, or if edu.school_name matches, list the ones whose education match the keyword.

I hope I could explain well :) Sorry for my english though.

---

Perhaps try an indexed view containing the merged dataset- you can add the fulltext index there instead of the individual tables, and it's further extensible to as many tables as you need down the line. Only trick, of course, is the space...

---

This is what I would do for my multi table full text search.

Not exact but it will give basic idea. the key thing is to give table vise contain with OR condition.

DECLARE  @SearchTerm NVARCHAR(250)
    
SET @SearchTerm = '"Texas*"'

SELECT * FROM table1 
JOIN table2 on table1.Id = table2.FKID
WHERE (
        (@SearchTerm = '""') OR 
        CONTAINS((table1.column1, table1.column2, table1.column3), @SearchTerm) OR 
        CONTAINS((table2.column1, table2.column2), @SearchTerm)
      )

---

Couple of points I don't understand that will be affecting your speed.

1. Do you really need a full outer join? That's killing you. It looks like these tables are one to one. In that case can't you make it an inner join?

2. Can't you pass a column list to contains like so:

   SELECT mem.member_id,
        mem.contact_name,
        edu.member_id,
        edu.school_name
   FROM members mem
       INNER JOIN education edu ON edu.member_id = mem.member_id
   WHERE Contains((mem.contact_name,edu.school_name),'*keyword*')
   ORDER BY mem.member_id DESC 
   

Further info about contains.