Overview | Criteria | Joins | Nested Queries | Distinct | Geographic Searches

A basic indexing strategy accommodates most queries, but there are advanced techniques that optimize more unusual situations. Queries with particularly complex criteria or complex table relationships can be optimized using these techniques. Omnidex also has features designed specifically to optimize distinct counts and geographic radius searches.

Before embarking on these techniques, it is important to understand Omnidex Query Plans. These plans describe the steps that are involved in processing a query, including the use of indexes. These query plans are essential to insuring the best performance for queries.

1. Optimizing Complex Criteria
2. Optimizing Nested Queries
3. Optimizing Complex Table Relationships
4. Optimizing Distinct Counts
5. Optimizing Latitude/Longitude Searches

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.

top of page

In this example, the HOUSEHOLD column is a primary constraint in HOUSEHOLDS and a foreign constraint in INDIVIDUALS.

select     INDIVIDUALS.NAME, HOUSEHOLDS.ADDRESS1, HOUSEHOLDS.ADDRESS2, INDIVIDUALS.PHONE
  from     INDIVIDUALS join HOUSEHOLDS on INDIVIDUALS.HOUSEHOLD = HOUSEHOLDS.HOUSEHOLD
 where     HOUSEHOLDS.STATE = 'CO' and INDIVIDUALS.NAME = 'John' 

For relational databases, the HOUSEHOLD column in INDIVIDUALS should be an Omnidex index since it is a foreign key. For raw data files, the HOUSEHOLD column in HOUSEHOLDS should also be an Omnidex index since it is a primary key.

top of page

In this example, the COUNT(*) does not require any additional indexes.

select     count(*)
  from     INDIVIDUALS
  where    STATE = 'CO' and NAME = 'John'

The STATE and NAME indexes which were created to satisfy the criteria will also satisfy the requested count.

top of page

Ordering is usually optimized by creating an index containing all of the columns in the ORDER BY clause. The order of the column in the index must match the order of the columns in the ORDER BY clause. At present, only ascending ORDER BY clauses are optimized.

In this example, the ORDER BY clause is optimized using the same index used for processing criteria.

select     NAME, ADDRESS1, ADDRESS2, PHONE 
  from     INDIVIDUALS
  where    STATE in ('CA','CO') and NAME = 'John'
  order by STATE

The STATE index will be used to satisfy both the criteria and the ORDER BY clause.

Next

See also: