June 8, 2008 at 7:46 pm
I have FTI operating on a table column and found that contains(field,'*search_item*') works better than field like('%search_item%') the like returns more rows as well.
Everything else in the query is the same.
Any suggestions?
(oops that should have been "Full Text Indexing":cool: )
Graham Okely B App Sc
Senior Database Administrator
June 16, 2008 at 4:00 pm
The performance comes from the fact that field like('%search_item%') cannot use any index - it generates a table scan. Note that field like('search_item%') (without the leading "%") is a completely different story - if there is an index for field it will use it - but presumably that's not the result you want.
This is what FTI is for really - it uses the Microsoft full text searching support (you should be able to find the files it builds to support it from the properties of the full text index).
As far as the different results go, I'm not sure - what type of phrases are you looking for? I think the full text search stuff is quite clever about finding approximate matchesm whereas the "LIKE" predicate is a character-for-character match.
Tony
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply