This shows you the differences between two versions of the page.
|
admin:indexing:advanced:home [2011/01/14 15:06] els |
admin:indexing:advanced:home [2016/06/28 22:38] |
||
|---|---|---|---|
| Line 1: | Line 1: | ||
| - | {{page>:top_add&nofooter&noeditbtn}} | ||
| - | ====== Administration: Indexing Strategies ====== | ||
| - | |||
| - | ===== Basic Strategies ===== | ||
| - | |||
| - | **[[admin:indexing:advanced:home|Overview]]** | | ||
| - | [[admin:indexing:advanced:criteria|Complex Criteria]] | | ||
| - | [[admin:indexing:advanced:nested|Nested Queries]] | | ||
| - | [[admin:indexing:advanced:joins|Complex Joins]] | | ||
| - | [[admin:indexing:advanced:distinct|Distinct Counts]] | | ||
| - | [[admin:indexing:advanced:latlong|Geographic Searches]] | ||
| - | |||
| - | ---- | ||
| - | |||
| - | ==== Overview ==== | ||
| - | |||
| - | ===== Advanced Indexing Strategies ===== | ||
| - | |||
| - | 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 [[admin:plans:home|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. | ||
| - | |||
| - | - [[#Optimizing_complex_criteria|Optimizing Complex Criteria]] | ||
| - | - [[#Optimizing_nested_queries|Optimizing Nested Queries]] | ||
| - | - [[#Optimizing_complex_table_relationships|Optimizing Complex Table Relationships]] | ||
| - | - [[#Optimizing_distinct_counts|Optimizing Distinct Counts]] | ||
| - | - [[#Optimizing_latitude_longitude_searches|Optimizing Latitude/Longitude Searches]] | ||
| - | |||
| - | ==== Optimizing Complex Criteria ==== | ||
| - | |||
| - | 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. | ||
| - | |||
| - | == Example 1. Basic Criteria == | ||
| - | |||
| - | 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. | ||
| - | |||
| - | |||
| - | ==== ==== | ||
| - | [[#Omnidex_Indexing|top of page]] | ||
| - | |||
| - | ==== Optimizing Nested Queries ==== | ||
| - | |||
| - | ==== Optimizing Complex Table Relationships ==== | ||
| - | |||
| - | |||
| - | == Example 1. Table joins on primary and foreign constraints == | ||
| - | |||
| - | 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. | ||
| - | |||
| - | ==== ==== | ||
| - | [[#Omnidex_Indexing|top of page]] | ||
| - | |||
| - | |||
| - | ==== Optimizing Distinct Counts ==== | ||
| - | |||
| - | |||
| - | == Example 1. Ungrouped COUNT(*) aggregations == | ||
| - | |||
| - | 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. | ||
| - | |||
| - | |||
| - | ==== ==== | ||
| - | [[#Omnidex_Indexing|top of page]] | ||
| - | |||
| - | |||
| - | ==== Optimizing Latitude/Longitude Searches ==== | ||
| - | |||
| - | 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. | ||
| - | |||
| - | |||
| - | == Example 1. Single-column ORDER BY clauses == | ||
| - | |||
| - | 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. | ||
| - | |||
| - | |||
| - | |||
| - | ===== ===== | ||
| - | |||
| - | **[[admin:indexing:advanced:criteria|Next]]** | ||
| - | |||
| - | ====== Additional Resources ====== | ||
| - | |||
| - | See also: | ||
| - | |||
| - | {{page>:admin:indexing:see_also&nofooter&noeditbtn}} | ||
| - | |||
| - | {{page>:bottom_add&nofooter&noeditbtn}} | ||