Post reply

Problem with the Full Text Search

  • September 27, 2007 at 3:37 pm

    #175241

    Hi all,

    I'm new to using a Full Text Search. We have a web and database based Email application, and our users need to be able to search for their emails by providing keywords, which are used to scan the four TEXT columns: toEmail, ccEmail, Subject and Body. Using 'Like' doesn't seem to work well with Users getting occasional timeouts.

    I've created a Full Text catalog and FTI on all four columns on the Email table. I made sure FTI is Enabled and Started population, which to my surprise ran very fast, considering that the table is close to a million records large.

    Then I ran this next query:

    select * From tbEMail where userID = 1111 AND CONTAINS (*, 'had')

    I expect to get a lot of records back, but I get none.

    When I run

    select * From tbEMail where userID = 1111 AND subject like '%had%'

    I do get a lot of records back.

    Can someone tell me what I'm missing here? Is it possible to check if the Full Text Index has been fully loaded?

    Thanks for your help in advance.

  • September 27, 2007 at 4:22 pm

    #736962

    Take a look at:

    sys.dm_fts_population_ranges

    sp_help_fulltext_tables

    FULLTEXTCATALOGPROPERTY ('catalog_name' ,'PopulateStatus')

    --Also MergeStatus and ItemCount

  • September 27, 2007 at 4:26 pm

    #736964

    Heh - just realized. "had" is a noise word. Full-text won't work on that one unless you remove it from the file (assuming that you are using the US English noise word file).

    Have you tried it with another word?

  • September 27, 2007 at 5:00 pm

    #736970

    Thanks so much for your help!

    It makes sense now....I wasn't aware of the 'noise' words.

    It does seem to work when passign other strings.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply