Multiple columns full text search doesn't seems to work properly

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Xavi - 23 Apr 2007 12:14 GMT

Hi all !

I have a full text search index on a table with several fields
indexed.

I tried the following query :

SELECT     MyIndexedTable.MyIndexedTableID
    MyIndexedTable.Title,
    KEY_TBL.RANK RANK_Total
FROM
    MyIndexedTable
INNER JOIN
    CONTAINSTABLE(MyIndexedTable,*,'"sport" AND "news"') AS KEY_TBL
ORDER BY KEY_TBL.RANK DESC

There are some records that are not returned by the query but they
have the words "sport" and "news" in their fields.

Any idea what could be happening?

Thanks in advance.

Xavi

Reply to this Message

Hilary Cotter - 23 Apr 2007 13:31 GMT

This should work. Note that sports and news will have to be in the same
column to get a hit form this row.

> Hi all !
>
[quoted text clipped - 20 lines]
>
> Xavi

Reply to this Message

Xavi - 23 Apr 2007 14:46 GMT

Exactly but I'd like to know how to make it work when words are
located in different fields.

I have read about creating an extra Text (or nText) field with all the
content in it and then create an index based on that field, I also
have seen something using unions and multiple querys on the index. I
wonder if there's any better approach and which one will give better
perfomance.

Thank you for your answer!

Xavi

> This should work. Note that sports and news will have to be in the same
> column to get a hit form this row.

Reply to this Message

Hilary Cotter - 23 Apr 2007 15:03 GMT

The concatenated column offers the best performance.

> Exactly but I'd like to know how to make it work when words are
> located in different fields.
[quoted text clipped - 11 lines]
>> This should work. Note that sports and news will have to be in the same
>> column to get a hit form this row.

Reply to this Message