search in fulltextindexes for multiple searchterms in multiple col

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Bart - 23 Mar 2007 10:00 GMT

I want to search in fulltextindexes for multiple searchterms in multiple
columns. The difficulty is:
I don't want only the records with columns that contains both searchterms.
I also want the records of which one column contains one of the searchterm
ans another column contains one of the searchterms.

For example I search for NETWORK and PERFORMANCE in two columns.
Jobdescr | Jobtext
Bad NETWORK PERFORMANCE | Slow NETWORK browsing in Windows XP
Bad application PERFORMANCE | Because of slow NETWORK browsing,
the application runs slow.

I only get the first record because JobDescr contains both searchterms
I don't get the second record because none of the columns contains both
searchterms

I managed to find a workaround:

SELECT T3.jobid, T3.jobdescr
FROM (SELECT jobid FROM dba.job WHERE contains(jobdescr, 'network*') or
CONTAINS(jobtext, 'network*') ) T1
INNER JOIN (SELECT jobid FROM dba.job WHERE contains(jobdescr,
'performance*') or CONTAINS(jobtext, 'performance*')) T2 ON T2.Jobid =
T1.Jobid
INNER JOIN (SELECT jobid, jobdescr FROM dba.job) T3 ON T3.Jobid = T1.Jobid
OR T3.Jobid = T2.JobId
It works but i guess this will result in a heavy database load when the
number of searchterms and columns will increase.

Does anyone know a better solution?

Thanks in advance Bart Rouw

Signature

Bart Rouw || Zwolle - Netherlands

Reply to this Message

Daniel Crichton - 23 Mar 2007 12:14 GMT

Bart wrote on Fri, 23 Mar 2007 02:00:00 -0700:

> I want to search in fulltextindexes for multiple searchterms in multiple
> columns. The difficulty is:
[quoted text clipped - 28 lines]
>
> Thanks in advance Bart Rouw

What version of SQL Server are you using?

With 2005 (not sure if * is supported for the column name in 2000), I'd do this:

SELECT jobid, jobdescr
FROM dba.job
WHERE CONTAINS (*,'network*')
AND CONTAINS(*,'performance*')

assuming that you only have the jobdescr and jobtext columns indexed.

Or instead of using CONTAINS, use CONTAINSTABLE instead for possibly better
performance (and assuming that jobid is your primary key)

SELECT T3.jobid, T3.jobdescr
FROM dba.job T3
INNER JOIN CONTAINSTABLE(dba.job,*,'network*') T1 ON T1.[KEY] = T3.jobid
INNER JOIN CONTAINSTABLE(dba.job,*,'performance*') T2 ON T2.[KEY] = T3.jobid

You could also use CONTAINSTABLE in your original query to possibly improve performance:

SELECT T3.jobid, T3.jobdescr
FROM dba.job T3
LEFT JOIN CONTAINSTABLE(dba.job,jobdescr, 'network*') T2 ON T3.jobid =
T2.[KEY]
LEFT JOIN CONTAINSTABLE(dba.job,jobtext, 'network*') T1 ON T3.jobid =
T1.[KEY]
LEFT JOIN CONTAINSTABLE(dba.job,jobdescr, 'performance*') T4 ON T3.jobid =
T4.[KEY]
LEFT JOIN CONTAINSTABLE(dba.job,jobtext, 'performance*') T5 ON T3.jobid =
T5.[KEY]
WHERE (T2.[KEY] IS NOT NULL OR T1.[KEY] IS NOT NULL) AND (T4.[KEY] IS NOT
NULL OR T5.[KEY] IS NOT NULL)

because or the requirement to have both words in at least one column, you
need to left join the main table to the FTS result tables and then check for
which ones have returned matching keys - if you used just an inner join
you'd only get results where both words occurred in both columns.

I think the syntax is correct, but I did write it off the top of my head so
I might have missed something.

For ease of handling a flexible number of search terms I'd encourage trying
to use one of the first 2 examples and only have the FTI on the columns you
want to search in together, as you can see it's much simpler to use the 1st
example by just adding more AND CONTAINS for each term, although the 2nd
example should be more efficient and is still pretty easy to add terms with.

Dan

Reply to this Message