Using FullText Search for file names

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

unique4 - 07 Aug 2008 13:25 GMT

Hello,

I have an SQL Server 2005 database storing file names (millions), and I
currently use LIKE to search the database - most of the time I need to do
searches using a part of the file name. The search is very slow right now.

Will using a FullText index help with performance in this particular
situation? I mean the file names are not really full words in English, so I'm
not sure if this is the right approach.

Thank you

Reply to this Message

Hilary Cotter - 08 Aug 2008 10:43 GMT

If you want to search on file extenstions, file names (ie the Document1 in
document1.doc), or wildcard is 'Doc*' yes, fts will work great for you.

If you want to search for tokens in the middle, ie ment in document, it
won't work at all.

> Hello,
>
[quoted text clipped - 8 lines]
>
> Thank you

Reply to this Message

sql411@nospam.com - 12 Aug 2008 22:55 GMT

Searching for part of the filename will work with fulltext, as long as your
wildcard is at the end of the phrase and not the beginning.

If you filename is 'myfilenameisthis.doc' and you do a search like:

select *
from table
where contains(filename, 'myfilename*')

then it should work, but this won't:
select *
from table
where contains(filename, '*myfilename')

Reply to this Message

unique4 - 15 Aug 2008 10:00 GMT

As far as I know, this limitation applies when using LIKE with a normal
index on a string-type column: queries with wildcards at the end benefit from
the index, those starting with wildcards do not benefit (full table scan).

Are you sure this applies to FullText as well?

Thanks

Reply to this Message

Daniel Crichton - 18 Aug 2008 13:45 GMT

unique4 wrote on Fri, 15 Aug 2008 02:00:03 -0700:

> As far as I know, this limitation applies when using LIKE with a normal
> index on a string-type column: queries with wildcards at the end
> benefit from the index, those starting with wildcards do not benefit
> (full table scan).

> Are you sure this applies to FullText as well?

> Thanks

Check the Books Online - FTS only handles a wildcard at the end, as already
stated.

Signature

Dan

Reply to this Message