This is an old revision of the document!
Overview | Complex Criteria | Nested Queries | Complex Joins | Distinct Counts | 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.
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, 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.
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.
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.
See also: