Show page
Old revisions

Differences

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

Link to this comparison view

Both sides previous revision Previous revision
Next revision 		Previous revision
admin:indexing:advanced:home [2011/01/14 15:17]
els 		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}}
  
 ====== Administration:​ Indexing Strategies ====== ====== Administration:​ Indexing Strategies ======
  
-===== Basic Strategies =====+===== Advanced ​Strategies =====
  
 **[[admin:​indexing:​advanced:​home|Overview]]** |  **[[admin:​indexing:​advanced:​home|Overview]]** | 
 [[admin:​indexing:​advanced:​criteria|Criteria]] |  [[admin:​indexing:​advanced:​criteria|Criteria]] | 
 [[admin:​indexing:​advanced:​joins|Joins]] |  [[admin:​indexing:​advanced:​joins|Joins]] | 
-[[admin:​indexing:​advanced:​nested|Nested Queries]] |  +[[admin:​indexing:​advanced:​subqueries|Subqueries]] |  
-[[admin:​indexing:​advanced:​distinct|Distinct]] |  +[[admin:​indexing:​advanced:​distinct|Distinct]] | 
-[[admin:​indexing:​advanced:​latlong|Geographic Searches]]+[[admin:​indexing:​advanced:​aggregations|Aggregations]] | 
 +[[admin:​indexing:​advanced:​ordering|Ordering]]
  
 ---- ----
Line 16: Line 19:
 ==== Overview ==== ==== Overview ====
  
-===== Advanced Indexing Strategies ===== +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.
- +
-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.+
  
 +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.
  
 +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.
  
 =====  ===== =====  =====
 
Back to top
admin/indexing/advanced/home.1295018255.txt.gz · Last modified: 2016/06/28 22:38 (external edit)
Sitemap Recent Changes