VistaDB 5
How To - Use Full Text Search

FTS indexes allow you to search through the textual fields in a database looking for words or phrases much quicker than using the LIKE operator. The LIKE operator only allows you to work with character patterns, full text queries can perform much more complex queries against textual data.

FTS indexes are best utilized when you have large quantities of unstructured data. The LIKE operator must run through every row during a query to determine if the characters exist. The FTS CONTAINS query only has to look in the FTS Index for the words and then lookup the resulting rows.

All FTS Updates are realtime

There is a performance penalty in using FTS indexes. Each update, insert and delete operation must now also update the FTS Index. Depending upon the size of the text fields in question this can take some time. The text fields must all be tokenized and then inserted into the FTS index.  This is different than SQL Server where the FTS Indexes are only updated when the SQL Agent runs to perform the update. VistaDB is client based and has no way to create a process later to update your FTS indexes.

It is generally considered a best practice to not use FTS indexes on data that is rapidly changed. FTS gives the best return on CPU expense when the data is generally static that needs to be searched.

VistaDB does not have the MS SQL Server requirement that the table contain a single unique non-null column.

Please view the following topics for more information:

What is a full text search index?

Create a full text search index

Query a full text index

Usage Notes

The current implementation in VistaDB should be considered an initial implementation. There are a number of advanced features still to be added to the FTS engine. VistaDB does not fully support all the FTS features that MS SQL Server does.

You may also want to read the topic on Full text search terminology for an overview of the terms used.

Please take a look at the provided NUnit examples for FTS from SQL. The current implementation does not allow for searching the FTS indexes from DDA, but you can create and remove them.

The current implementation is for the English language only.

See Also