Omnidex Indexing

Overview → Indexes → Strategies → Advanced → Installation → Maintenance

The basic strategy for creating Omnidex indexes is to index all of the columns that are used in criteria, table joins, aggregations and ordering. This is a different approach than is taken with relational databases. Database administrators are usually trained to identify selected columns that are most frequently used and create indexes only on those tables. Their general expectation is that the relational database will choose an index to access a table and will process the rest of the statement by directly evaluating the data.

Omnidex approaches indexing differently. Omnidex indexes all of the columns and then coordinates searches across all of the indexes to fulfill the different aspects of a query. Some indexes will be use to satisfy table joins or criteria. Other indexes will be used to fulfill aggregations or ordering. Most database administrators are surprised to learn that complex SQL statements which join many tables and contain intricate criteria can often be fulfilled without ever accessing the underlying data. This approach allows Omnidex to process queries very quickly. It also reduces the load on the servers since accessing the data is a common cause of performance problems.

Most indexing strategies are developed by analyzing the queries. Queries usually follow patterns and these patterns give clues to the best indexing approach. Analyze the queries to determine which columns are criteria in the WHERE clause, which tables are joined together in the FROM clause, which columns are aggregated or used in GROUP BY clauses, and which columns are used in the ORDER BY clause. Use the discussions below to determine indexes for these columns and then run sample queries to assess their performance. Query plans will show how the indexes were used, and if the query is not fully optimized, revisit the indexing strategy as needed. You can also read about Advanced Indexing Strategies for special optimization techniques.

1. Optimizing Criteria
2. Optimizing Table Joins
3. Optimizing Aggregations
4. Optimizing Ordering
5. Other Uses for Indexes

Criteria is usually optimized by creating indexes on each column. This is true regardless of the use of Boolean operators or parentheses. As you consider the query patterns, look for opportunities to use QuickText indexes or indexing options.

In the following statement, look for the columns used as criteria in the WHERE clause

select name, address1, address2, phone
  from individuals
 where ((state = 'CO' and city = 'Boulder') or 
        (state = 'IL' and city = 'Chicago'))

The STATE and CITY columns should be Omnidex indexes.

In this example, you might want to use QuickText indexes.

select name, address1, address2, phone
  from individuals
 where state = 'CO' and name = 'John'

The STATE column should be an Omnidex index and the NAME column should be a Quicktext index.

In this same example, we might want to allow case insensitivity.

select name, address1, address2, phone
  from individuals
 where state = 'co' and name = 'john'

The STATE column should be an Omnidex index with the Case Insensitive option, and the NAME column should be a QuickText index. QuickText indexes are automatically case insensitive.

The next section on Omnidex Indexing is Basic Indexing Strategies.