Will there be performance issues when I (incorrectly) use a database index table as a full-text search index?
At work, we had a discussion about adding a full text search engine to our product. One suggestion was to use the MSSQL database table as a full text search index, for example:
Keyword Document
------------------
This Doc1
Is Doc1
A Doc1
Test Doc1
And Doc2
This Doc2
Too Doc2
The search will contain all strings containing a word grouped by document.
The EDIT: . This table will contain> 50 Mio. strings.
Just to clarify: we don't want to use MSSQL full-text functions.
Be blunt: I don't like this and would rather use Lucene, but since our product is written in VB6, it will take some work. So for the time being we will do it as I described, unless I come up with some hard facts against him. Or maybe you can convince me that this is not such a bad idea.
a source to share
What you describe is actually the way many forums ( phpBB ) work. So yeah, this is a way to do it that has been used by other people, and it works really well. Disadvantages:
- You cannot do things like "NEAR word", only "AND / OR word"
- The tables are growing rapidly. Full-text search engines compress words by removing plurals, adding common prefixes, etc., to compress the search space.
- It's relatively easy to do a denial of service attack from these things, since lookups pull in a lot of data (at least for many forums this was a big problem).
One small optimization: create a table with words and assign each word an identifier (32 bits of int, English only has about 2 million words, including names, etc.). Then create a mapping M: N "word-id" to "doc-id". This will significantly reduce the size of the database.
a source to share
If you are using VB6 and want to use Lucene, get clucene , create it as a dll (maybe with a C wrapper to expose the AddDocument and SearchFiles functions) and call them from your VB6 code. (or create a simple COM object to do the same and call this)
the data can then be parsed and presented to your user just as easily as hacking your DB, and there will be a lot less trouble, especially when you constantly update your data table with new additions.
What will you do with repeating words? It would be awfully slow to update the DB with every word in every document (if you expect 50 million unique ones!), And how would you handle adding new files without just adding each word (unless you distinguish between the document and only update added words or delete deleted ones) - what a trouble!)
It may take you a day or so to take clucene.
a source to share