Search in multiple tables with Full-Text

Question

Hi,

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 - Education

• 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.

Answer 1

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.

Answer 2

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...