Fts design question. - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Fts design question.

Author

Topic

slacker
Posting Yak Master

115 Posts

Posted - 2003-05-30 : 07:01:42

Hey guys. I didnt get a chance to thank you for answering my last FTS question I just read your responses and it was really useful. I have yet another question :p. We are trying to create better relevancy by columnname. Basically certain columns regardless of the contents of another column should always contain the best ranking. This isnt always true when using standard contains clause. After reading some questions posted the general idea seems to be to JOIN two CONTAINSTABLE statements together and give the one with higher ranking a higher multiplier. This is really slow. A couple of other ideas floating around are...

Create another table with the fields we are using in fts and the primary key. With the fields we want higher rank duplicate the contents of the orignal column like 10 times so that fts see's more words and then gives it a better ranking.. then join it with the source table.

--example of what i mean

create table Movies (
MovieID INT,
Title VARCHAR(100),
Description VARCHAR(1000),
ReleaseDate DATETIME
)
insert into Movies(Title,Description,ReleaseDate)
values('The Matrix Reloaded','Cool movie',GETDATE())

-- This table is what is actually fts'd
create table MoviesFTS (
MovieID int,
Title VARCHAR(100),
Description varchar(1000)
)

-- since we want title to have a better relevancy replace the contents
-- of title over and over to force better ranking

insert into MoviesFTS(Title,Description)
values('The Matrix Reloaded The Matrix Reloaded The Matrix Reloaded The Matrix Reloaded The Matrix Reloaded The Matrix Reloaded The Matrix Reloaded The Matrix Reloaded The Matrix Reloaded The Matrix Reloaded','Cool movie',GETDATE())

-- select statement
select Movies.* FROM Movies
INNER JOIN CONTAINSTABLE(MoviesFTS,*,'Matrix AND Reloaded') fText
ON fText.[Key]=Movies.MovieID
ORDER BY fText.Rank DESC

Or my other idea which would be to use a CASE statement
and use like on the field in question.

SELECT *,
CustomRank = CASE
WHEN Title Like '%Matrix%Reloaded%' THEN 10000 -- force better rank
Else Ftext.Rank
END
FROM Movies INNER JOIN CONTAINSTABLE(Movies,*,'Matrix AND Reloaded') Ftext ON Ftext.[Key]=Movies.MovieID
ORDER BY CustomRank DESC

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources