Search in multiple full-text tables

Question

I am trying to do a detailed search with the full text of asp and SQL Server.

When sending a keyword, I need to search in multiple tables. For instance,

Table - Members

• member_id
• contact_name

Table - Education

• member_id
• school_name

My request;

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 a very long time to complete.

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

Hope I could explain well :) Sorry for my English though.

Answer 1

A few points I do not understand what will affect your speed.

• Do you really need a full outer join? This is killing you. It looks like these tables are one to one. In this case, you cannot make it an inner join?

• You cannot pass a list of columns that looks like this:

   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 

Additional information on contains .

Answer 2

Perhaps try an indexed view containing a combined dataset - you can add the full text, not separate tables, and it will expand to as many tables as you need. Of course, only the trick is space ...