This is an old revision of the document!
Overview → Indexes → Strategies → ActiveCounts → PowerSearch → Advanced → Installation
Three types of indexes provide the bulk of Omnidex functionality. Omnidex indexes are used to optimize criteria, table joins, aggregations and ordering. QuickText indexes are used to index small text fields, such as names, addresses, product descriptions and general text. FullText indexes are used to index large blocks of text. Omnidex also allows custom indexes that employ specialized options which are appropriate for specific situations.
Omnidex also supports expression-based indexing by allowing the creation of virtual columns that are comprised by a SQL expression. Even though this column does not physically exist in the database, it can be indexed, queries and retrieved just like any other column in the database.
Omnidex allows multiple indexes to be gathered into an Index Group. Queries that reference columns within an Index Group automatically search all of the other columns in that group. This is useful for grouping a first name with aliases, grouping multiple phones or emails together, or grouping address lines together.
Indexes can be prejoined with a parent table to improve the performance of table joins. A prejoined index stores both parent and child table pointers to improve the speed of table joins.
These indexing capabilities provide a versatile suite of tools to optimize a wide variety of queries.
There are several different types of Omnidex indexes. Each is designed for a different scenario, and all can be used in the same search. For each column that is indexed, choose the index type that best meets the needs of the queries.
Omnidex indexes provide the bulk of Omnidex functionality. These indexes are placed on columns that are involved in Omnidex criteria, table joins, aggregations and ordering. Omnidex indexes can also be used to retrieve data without having to access the database.
Typically, administrators place Omnidex indexes on all columns that are involved in the SQL statement. The Omnidex SQL Engine will use each index in different ways depending on how the corresponding column is used in the SQL statement. In a given query, some indexes will satisfy criteria or support table joins, while other indexes will fulfill aggregations or ordering. In a subsequent query, those same indexes may get used for different purpose.
An Omnidex index can also be created with multiple columns. These indexes are valuable for optimizing SQL statements with multiple columns in the GROUP BY clause, ORDER BY clauses or the aggregation functions (COUNT, SUM, MIN, MAX and AVERAGE).
QuickText indexes are specialty indexes designed for small blocks of text, such as names, addresses, descriptions and general text. QuickText indexes parse the contents of the column so that each word is indexed separately. Queries can then mention just a few of the words in that column rather than having to mention the whole field. For example, a field containing the name “John Q. Doe” can be located by just asking for “John” or “John Doe”. QuickText indexes provide basic text searches at the same speed as Omnidex indexes.
FullText indexes are specialty indexes designed for large blocks of text, such as abstracts, articles and text documents. FullText indexes parse the contents of the column so that each word is indexed separately. FullText indexes also track the position of each word in the field to aid in providing relevancy scores. Queries can also use special syntax to require that one word be a certain distance from other word. FullText indexes necessarily have more overhead than QuickText indexes.
Omnidex also supports Custom Indexes which allow more granular control over the options used on an index. Custom indexes can also be used to index a portion of a column or a concatenation of multiple columns.
Indexes have several options that can be used. The most common option is Case Sensitivity. By default, Omnidex indexes are case sensitive, just like relational database indexes. QuickText and FullText indexes are case insensitive, as is appropriate for textual searches. These can be easily changed when creating an individual index.
Omnidex allows SQL expressions to be indexed. Virtual columns can be created that are based on a SQL expression, similar to the approach used in relational views. These columns can then be indexed, queried and retrieved just like a normal column, even though they do not physically exist in the database.
Expression-based columns can be used to index portions of a field using the SUBSTRING function, such as the area code within a phone number. They can be use to index the concatenation of several columns, such as the combinations of all portions of an address. They can be used to perform conditional indexing using the CASE function, such as indexing an phone number only if that number is eligible to be called.
Omnidex indexes and QuickText indexes can be combined into Index Groups. Criteria against one column in an Index Group will be applied to all columns in the group. This is useful for situations where similar data is stored in separate columns. Typical uses of Index Groups are to search first names along with their aliases, search multiple lines of an address field, or search multiple phone numbers. Index Groups can also include Pre-joined Indexes, allowing searches to automatically span multiple tables.
Omnidex allows indexes in a child table to also store pointers to the parent table. This improves the performance of joins between that child and its parent. These indexes require more overhead, but that can be easily offset by the performance gains on queries.