This shows you the differences between two versions of the page.
| Both sides previous revision Previous revision Next revision | Previous revision | ||
|
admin:indexing:basic [2009/12/09 18:04] els |
admin:indexing:basic [2012/10/26 14:26] (current) |
||
|---|---|---|---|
| Line 22: | Line 22: | ||
| ===== Optimizing Criteria ===== | ===== Optimizing Criteria ===== | ||
| - | |||
| - | [[#Omnidex_Indexing|top]] | ||
| 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. | 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. | ||
| Line 31: | Line 29: | ||
| In the following statement, look for the columns used as criteria in the WHERE clause | In the following statement, look for the columns used as criteria in the WHERE clause | ||
| - | select name, address1, address2, phone | + | select NAME, ADDRESS1, ADDRESS2, PHONE |
| - | from individuals | + | from INDIVIDUALS |
| - | where ((state = 'CO' and city = 'Boulder') or | + | where ((STATE = 'CO' and CITY = 'Boulder') or |
| - | (state = 'IL' and city = 'Chicago')) | + | (STATE = 'IL' and CITY = 'Chicago')) |
| The STATE and CITY columns should be Omnidex indexes. | The STATE and CITY columns should be Omnidex indexes. | ||
| Line 42: | Line 40: | ||
| In this example, you might want to use QuickText indexes. | In this example, you might want to use QuickText indexes. | ||
| - | select name, address1, address2, phone | + | select NAME, ADDRESS1, ADDRESS2, PHONE |
| - | from individuals | + | from INDIVIDUALS |
| - | where state = 'CO' and name = 'John' | + | where STATE = 'CO' and NAME = 'John' |
| The STATE column should be an Omnidex index and the NAME column should be a Quicktext index. | The STATE column should be an Omnidex index and the NAME column should be a Quicktext index. | ||
| Line 52: | Line 50: | ||
| In this same example, we might want to allow case insensitivity. | In this same example, we might want to allow case insensitivity. | ||
| - | select name, address1, address2, phone | + | select NAME, ADDRESS1, ADDRESS2, PHONE |
| - | from individuals | + | from INDIVIDUALS |
| - | where state = 'co' and name = 'john' | + | where STATE = 'co' and NAME = 'john' |
| The STATE column should be an Omnidex index with the Case Insensitive option, and the NAME column should be a QuickText index. QuickText indexes are automatically case insensitive. | The STATE column should be an Omnidex index with the Case Insensitive option, and the NAME column should be a QuickText index. QuickText indexes are automatically case insensitive. | ||
| + | |||
| + | ===== ===== | ||
| + | [[#Omnidex_Indexing|top of page]] | ||
| ===== Optimizing Table Joins ===== | ===== Optimizing Table Joins ===== | ||
| + | |||
| + | Tables joins fall into two main categories. Some joins provide access to tables that are involved in criteria. Other joins provide access to tables that are only used for select items. Most of the time, Omnidex is only concerned about the tables that are involved in criteria. Table relationships can get complicated, so this rule doesn't always hold true. | ||
| + | |||
| + | Table joins are usually optimized by indexing the foreign keys in the child tables. Most table joins follow either a declared or implied constraint, meaning that they follow the columns that form a parent-child relationship between tables. In most situations, Omnidex will want try to start in the parent and join to the child. This means that the foreign key of that child must be indexed. | ||
| + | |||
| + | == Example 1. Joins that do not involve criteria == | ||
| + | |||
| + | In this example, the parent table is only used for select items and is not used for criteria. | ||
| + | |||
| + | select INDIVIDUALS.NAME, HOUSEHOLDS.ADDRESS1, HOUSEHOLDS.ADDRESS2, INDIVIDUALS.PHONE | ||
| + | from INDIVIDUALS join HOUSEHOLDS on INDIVIDUALS.HOUSEHOLD = HOUSEHOLDS.HOUSEHOLD | ||
| + | where INDIVIDUALS.NAME = 'John' | ||
| + | |||
| + | The parent table HOUSEHOLDS is only used to obtain select items and is not used in criteria. For relational databases, the NAME column should be indexed to resolve the criteria, but no indexes are needed to optimize the join. Omnidex will identify the INDIVIDUALS rows based on the NAME index, and then Omnidex will ask the relational database to join to the HOUSEHOLDS table. | ||
| + | |||
| + | For raw data files, there is no underlying database. Omnidex must provide all of the indexing. In this situation, the primary and foreign constraints must be indexed with Omnidex, causing indexes on HOUSEHOLDS.HOUSEHOLD and INDIVIDUALS.HOUSEHOLD. | ||
| + | |||
| + | == Example 2. Joins that do involve criteria == | ||
| + | |||
| + | In this example, the parent table is used for criteria as well as select items. | ||
| + | |||
| + | 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' | ||
| + | |||
| + | The parent table HOUSEHOLDS is used to obtain select items and is also used in criteria. For relational databases, the INDIVIDUALS.HOUSEHOLD column must be indexed since it is the foreign key. The NAME column should also be indexed to resolve the criteria. Omnidex will first qualify the HOUSEHOLDS based on the HOUSEHOLDS.STATE index, then join from HOUSEHOLDS to INDIVIDUALS using the INDIVIDUALS.HOUSEHOLD index, and further qualify the INDIVIDUALS based on the INDIVIDUALS.NAME index. | ||
| + | |||
| + | For raw data files, all primary and foreign constraint columns should be indexed since there is no underlying database. | ||
| + | |||
| + | |||
| + | ===== ===== | ||
| + | [[#Omnidex_Indexing|top of page]] | ||
| ===== Optimizing Aggregations ===== | ===== Optimizing Aggregations ===== | ||
| + | |||
| + | ===== ===== | ||
| + | [[#Omnidex_Indexing|top of page]] | ||
| ===== Optimizing Ordering ===== | ===== Optimizing Ordering ===== | ||
| + | |||
| + | ===== ===== | ||
| + | [[#Omnidex_Indexing|top of page]] | ||
| ===== Other Uses for Indexes ===== | ===== Other Uses for Indexes ===== | ||
| + | ===== ===== | ||
| + | [[#Omnidex_Indexing|top of page]] | ||