Show page
Old revisions

Differences

This shows you the differences between two versions of the page.

Link to this comparison view

Next revision 		Previous revision
admin:indexing:advanced:home [2010/07/29 03:45]
127.0.0.1 external edit 		admin:indexing:advanced:home [2016/06/28 22:38] (current)
Line 1: Line 1:
 +~~NOTOC~~
 +
 {{page>:​top_add&​nofooter&​noeditbtn}} {{page>:​top_add&​nofooter&​noeditbtn}}
  
-====== ​Omnidex ​Indexing ======+====== ​Administration: ​Indexing ​Strategies ​======
  
-[[admin:​indexing:​home|Overview]] -> [[admin:​indexing:​indexes|Indexes]] -> [[admin:​indexing:​basic|Strategies]] -> [[admin:​indexing:​activecounts|ActiveCounts]] -> [[admin:​indexing:​powersearch|PowerSearch]] -> **[[admin:​indexing:​advanced|Advanced]]** -> [[admin:​indexing:​installation|Installation]]+===== Advanced ​Strategies =====
  
 +**[[admin:​indexing:​advanced:​home|Overview]]** | 
 +[[admin:​indexing:​advanced:​criteria|Criteria]] | 
 +[[admin:​indexing:​advanced:​joins|Joins]] | 
 +[[admin:​indexing:​advanced:​subqueries|Subqueries]] | 
 +[[admin:​indexing:​advanced:​distinct|Distinct]] |
 +[[admin:​indexing:​advanced:​aggregations|Aggregations]] |
 +[[admin:​indexing:​advanced:​ordering|Ordering]]
  
-===== 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.  ​+==== Overview ====
  
-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.+A basic indexing strategy accommodates most queries, but there are advanced ​techniques ​that optimize more challenging 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 other complex aggregations.
  
-  - [[#​Optimizing_complex_criteria|Optimizing Complex Criteria]] +Before embarking on these techniques, it is important to understand ​[[admin:​optimization:​plans:​home|Omnidex Query Plans]].  These plans describe the steps that are involved in processing a query, including the use of Omnidex indexes. ​ These query plans are essential to insuring the best performance for queries.
-  - [[#​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 ====+The advanced strategies described here can be used to optimize a wide variety of queries. ​ Even so, there are some queries that would benefit from review by one of our engineers. ​ If you are not able to achieve the level of performance you desired with a query, contact [[appendix:​contactus|Technical Support]]. We can sometimes offer suggestions as part of your support contract, and if needed, we can discuss having an engineer analyze your environment and your query plans to achieve improved performance.
  
-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 ==+**[[admin:​indexing:​advanced:​criteria|Next]]**
  
-In the following statement, look for the columns used as criteria in the WHERE clause +====== ​Additional Resources ​======
- +
-  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. +
- +
- +
- +
-==== ==== +
-[[#​Omnidex_Indexing|top of page]] +
- +
- +
-===== Validating the Indexing Strategy ===== +
- +
- +
----- +
-\\  ​+
  
-The next section on Omnidex Indexing is [[admin:indexing:​installation|Index Installation]].+See also: 
  
-**[[admin:​indexing:powersearch|Prev]]** | **[[admin:​indexing:​installation|Next]]**+{{page>:​admin:​indexing:​see_also&​nofooter&​noeditbtn}}
  
 {{page>:​bottom_add&​nofooter&​noeditbtn}} {{page>:​bottom_add&​nofooter&​noeditbtn}}
 
Back to top
admin/indexing/advanced/home.1280375142.txt.gz · Last modified: 2016/06/28 22:38 (external edit)
Sitemap Recent Changes