sql server full text search: Help with returning too much data -- sql server full text search

Re: Help with returning too much data

Mike Collins
2/14/2007 5:52:05 AM

Not sure what you mean by "are they integer values", but the table that
contains MyFields is full-text indexed. Do tables 1,2,3, and 4 need to be
full-text indexed? Course, I'm thinking yes since you asked the question :)

[quoted text, click to view]

Re: Help with returning too much data

Mike Collins
2/14/2007 7:40:43 AM

Sorry, but our company specifically prohibits posting any schema details in
newsgroups, but should the order be:
1. Remove words from the noise list.
2. Create the full-text index.

I'm wondering that since I created the index before removing the 4 from the
noise list, that it may be the reason my search is not working.

[quoted text, click to view]

Re: Help with returning too much data

Mike Collins
2/14/2007 11:35:25 AM

The join condition is there because there are many other items that we are
building a where clause on. I simplified the query to what I thought was most
necessary and only included the joins to be true to my actual query, but with
what you have said about creating the full-text after removing the number 4
from the noise file, now I do not think they matter. If I could show you the
actual schema, I think you would agree with me.

Thank you very much for your time. I'll recreate the full-text index, after
removing any noise words, and see how it works for me then.

[quoted text, click to view]

Re: Help with returning too much data

Hilary Cotter
2/14/2007 12:15:00 PM

OK, let me guess your schema then from what you have posted.

Create MyFields(PK int not null identity primary key, Fields1 char(20),
Field2 char(20), Field3 char(20), Field4 char(20))
Create T1 (pk int not null references MyFields(PK), Field1 char(20))
Create T2 (pk int not null references MyFields(PK), Field2 char(20))
Create T3 (pk int not null references MyFields(PK), Field3 char(20))
Create T4 (pk int not null references MyFields(PK), Field4 char(20))

This is kind of critical as I think your join condition is all wrong.

But you are correct with a search on mode-4 and you have removed 4 from your
noise word list after building your index you will not get correct results.
In fact you should get fewer results which makes me wonder about your join
condition.

--
Hilary Cotter

Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html

Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com

[quoted text, click to view]

Re: Help with returning too much data

Martin
2/20/2007 9:05:54 AM

MS changed the worbreaker in windows 2003, to contain what I consider
is now a bug.

The hyphen in 'mode-4' is actually now used to split the phrase into 2
words, therefore doing an OR search, hence returning every record with
'mode' OR '4' in it, which I expect will be quite a few.

On windows 2000, this worked properly to join the word, as a hyphen is
actually supposed to in text.

Eventually I got around it by replacing all hyphens in the indexed
text with HYP ie 'modeHYP4'. You need to replace any hyphens that
users enter in the search to the same.

Lots of our product skus had hyphens in so it was causing all sorts of
problems, 21-500 was returning thousands of results instead of 1.

Hope this helps....

On 14 Feb, 19:35, Mike Collins <MikeColl...@discussions.microsoft.com>
[quoted text, click to view]

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages in the sql server full text search group.