contact contact

B.08.20 / Release Notes / Database / FTS / Introduction

Introduction to FTS Indexes

  FTS is an indexing subsystem for the Eloquence database introduced with Eloquence B.08.20. FTS indexes may be used to add full text search capabilities to applications and support advanced search capabilities that would otherwise require a full database scan.


Before the advent of search engines, users had to search manually through dozens - or hundreds - of documents and tidbits to find what they were looking for. Nowadays we expect results to come to the user, not the other way around. The search engine gets the computer to do the work for the user.

While databases for long supported searching by indexes, offering ordered retrieval and partial key searches, this is of limited use if a word is not at the beginning of a field and also limits searches to a single field. A full scan of the database content is prohibitive once data grows beyond a trivial size.

Eloquence FTS adds search engine capabilities to the database, enabling applications to support advanced search capabilities with only minor changes.

FTS indexes extend on traditional indexes in a number of ways

  • FTS indexes are optimized to qualify result sets rather than individual records. While the result of an FTS search may qualify a single record, it may as well qualify a million records without taking much longer. For example, searching all customers in a specific city should take roughly the same time, regardless if there are only a few or a few thousand.

  • FTS searches may be combined or filtered subsequently. Boolean operators, such as AND, OR or NOT may be applied to FTS searches. For example, one may search all customers in a specific state and subsequently filter out a specific city. Or even search for customers NOT in a specific city.

  • Searches on different items may be combined. For example, qualifying database content by region and recently purchased products.

  • Text fields may be parsed to index individual words, allowing to qualify database entries by keywords or combinations. For example, searching product descriptions for "torx" would qualify all torx screws (and screw drivers), regardless where this word occurs in the description. With traditional indexes this is only efficient if the searched word appears at the beginning of a field.

  • Fields may be grouped so searches may span multiple fields. For example, multiple address fields in a customer record could be used to qualify results.

FTS stucture

The FTS indexing subsystem uses a dictionary to maintain a list of unique words. For each word a result set identifies any database records using that word. FTS searches are usually performed within the dictionary and the associated result set rather than searching individual database records.

Full text search

Eloquence FTS indexes may be used to add full text search capabilities to applications, to efficiently lookup data for arbitrary words or combinations of words in indexed fields. While traditional indexes allow searching for partial values (at the beginning of a field), FTS indexes make it easy to add searching the database for arbitrary words, with just a few lines of code (or no application changes at all for simple use).

With FTS, text fields are parsed by default and the individual words are indexed. For example, a supplier "ACME Widget Factory" could be searched for by "acme", "widget" or "factory". Searching for widgets suppliers other than ACME is just as easy ("widget not acme"). Boolean operators (AND, OR, NOT) may be used to combine or exclude specific words.

By default FTS indexes are not case sensitive and searching for partial words, wildcards and ranges is supported. For example, searching for angel@ could qualify "Los Angeles".

FTS fields may be grouped, a word would match regardless of which fields it originally came from. For example, a customer address with name, street and city fields. When placed in the same group, words occurring in any of these fields could be used to qualify the customer.

As FTS searches may be combined with previous FTS results, it is easy to combine information from multiple fields (even if not grouped), providing complex searches with just a few lines of code. For example, when searching for customers in California but not in Redwood one could search for "State = ca" and subsequently filter the result by "city = and not redwood".

Advanced functionality

In addition to the basic FTS functionality the optional "FTS" license option enables additional functionality.
  • FTS indexes are no longer limited to a single data set but may refer to a related master set entry. This allows to maintain aggregated FTS indexes (any words occurring in either the master or any detail are attributed to the master record), to qualify both master and detail records or to combine queries on different sets with a common master set.

  • FTS indexes are not limited to text fields but extend to numeric items or numeric values in text fields. This allows to efficiently search for numeric values or ranges of numeric values. For example, to qualify orders from a region above a specified value.

  • Applying search results to other sets. Search results in one data set may be used to qualify related records in another data set.

  • Searching for phonetic representation (soundex). An index field may be designated to support soundex searches, making it easy to locate entries despite minor differences in spelling.

  • Fuzzy search allows matching mistyped words or phone numbers based on the similarity of words.

Backwards compatibility

As an option, Eloquence implements "ODX" calls to access the Eloquence FTS functionality. This allows existing applications relying on ODX/DBI calls to make use of Eloquence FTS indexes with no or only minor changes.

  Privacy | Webmaster | Terms of use | Impressum Revision:  2013-03-28  
  Copyright © 2012 Marxmeier Software AG