July 29, 2005 at 2:32 pm
We currently have a database named full text that stores all of the words that appear in a document. The database currently has one table named word index. It has 3 fields an identity field (rowid) and field named word that contains the word and document #. This database has a clustered index. The database is roughly 3.5 gigs in size. We would like to know which of these options is best and why.
1. Leave it this way
2. Create different tables based on the first letter of each word so that there would be an a table a btable and so on.
3. Create an individual database for each letter so that we would have 26 different databases with one table each(word index)
The application that uses the tables will only query on one word at a time.
Please let me know if you need any more information to suggest which way is best
Thank You
Jay
August 1, 2005 at 8:00 am
This was removed by the editor as SPAM
August 1, 2005 at 8:26 am
Is the clustered index on the Word or the RowID? (I assume the rowid is there simply to make a unique value
If your search app is trying to find all docs that have the world 'Hello' in them, then the RowID has absolutely no bearing on anything you just want the doc numbers
unless you are tracking whether the word appears x times in the document then the cobination of Word + Doc No would be the ideal candidate for a unique clustered index
Taking innto account the above I'd say stick with what you have got but possibly change the indexing strategy
Graham
August 1, 2005 at 8:41 am
I too would agree, if you do not already have a clustered index on the Word column, you should create it. This assumes that most (if not all) of your search queries against this table are word based and not document #.
Additionally, you may want to consider using Full Text Search (FTS) and creating a FT Index on Rowid and FT-enable column word, so this will add more flexiblity to your word searching, specificly inflectional word searches.
Thanks,
John
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
John T. Kane
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply