November 11, 2003 at 9:38 am
We have a table that contains two fields (both of the varchar data type) that we allow users to search. This table contains 45k+ rows - nothing substantial, but not very small either. Was wondering if full-text indexing would help speed up our searches? We commonly use the LIKE operator on the two varchar fields in the table when performing searches.
Please give me your opinions, and also some links to intructions on how to properly and efficiently setup Full Text Indexing keeping performance in mind.
Thanks!
November 11, 2003 at 8:57 pm
Hi there
I think you will be supprised at the speed benefits from fulltext, the figures differ of course based on your current search patterns and indexes in place. If you finding that character based searching is very much like '%something%' then full text can reall add value in terms of speed AND function. The functionality end of the stick comes with its ability to fuzzy search, search over multiple columns in a single hit, add in inflections etc... all very nice and perhaps gives users and "better" result set from which to work from.
Be aware that full text comes with some issues:
a) developers must trap for sql errors, namely around all search criteria the user typed in was filtered and resulted in an empty string (all search criteria was noise)
d) timestamp on your table for automatic updates
c) FTI searching over mult-indexed columnns for a single table can cause some interesting search issues in relation to the "ANDing" of values in the search clause vs the column values.
d) searching takes on a new light, you may need some pre-parsing stored procs to take the users criteria and make it "FTI search friendly" for better results (dont make it too complex though!)
note that automatic/background updates can take between 10 to 20 sec to reflect in the index, so take care with your app design on entry of new records.
Generally, Id say give it a go and i believe you will be impressed with the functionality and speed.
Cheers
Ck
Chris Kempster
Author of "SQL Server 2k for the Oracle DBA"
Chris Kempster
www.chriskempster.com
Author of "SQL Server Backup, Recovery & Troubleshooting"
Author of "SQL Server 2k for the Oracle DBA"
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply