This shows you the differences between two versions of the page.
| Both sides previous revision Previous revision Next revision | Previous revision | ||
|
admin:indexing:advanced:home [2011/01/14 15:06] els |
admin:indexing:advanced:home [2012/10/26 14:57] (current) |
||
|---|---|---|---|
| Line 1: | Line 1: | ||
| + | ~~NOTOC~~ | ||
| {{page>:top_add&nofooter&noeditbtn}} | {{page>:top_add&nofooter&noeditbtn}} | ||
| Line 6: | Line 7: | ||
| **[[admin:indexing:advanced:home|Overview]]** | | **[[admin:indexing:advanced:home|Overview]]** | | ||
| - | [[admin:indexing:advanced:criteria|Complex Criteria]] | | + | [[admin:indexing:advanced:criteria|Criteria]] | |
| - | [[admin:indexing:advanced:nested|Nested Queries]] | | + | [[admin:indexing:advanced:joins|Joins]] | |
| - | [[admin:indexing:advanced:joins|Complex Joins]] | | + | [[admin:indexing:advanced:subqueries|Subqueries]] | |
| - | [[admin:indexing:advanced:distinct|Distinct Counts]] | | + | [[admin:indexing:advanced:distinct|Distinct]] | |
| [[admin:indexing:advanced:latlong|Geographic Searches]] | [[admin:indexing:advanced:latlong|Geographic Searches]] | ||
| Line 36: | Line 37: | ||
| 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 | ||
| + | <code sql> | ||
| 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')) | ||
| + | </code> | ||
| The STATE and CITY columns should be Omnidex indexes. | The STATE and CITY columns should be Omnidex indexes. | ||
| Line 56: | Line 59: | ||
| In this example, the HOUSEHOLD column is a primary constraint in HOUSEHOLDS and a foreign constraint in INDIVIDUALS. | In this example, the HOUSEHOLD column is a primary constraint in HOUSEHOLDS and a foreign constraint in INDIVIDUALS. | ||
| + | <code sql> | ||
| select INDIVIDUALS.NAME, HOUSEHOLDS.ADDRESS1, HOUSEHOLDS.ADDRESS2, INDIVIDUALS.PHONE | select INDIVIDUALS.NAME, HOUSEHOLDS.ADDRESS1, HOUSEHOLDS.ADDRESS2, INDIVIDUALS.PHONE | ||
| from INDIVIDUALS join HOUSEHOLDS on INDIVIDUALS.HOUSEHOLD = HOUSEHOLDS.HOUSEHOLD | from INDIVIDUALS join HOUSEHOLDS on INDIVIDUALS.HOUSEHOLD = HOUSEHOLDS.HOUSEHOLD | ||
| where HOUSEHOLDS.STATE = 'CO' and INDIVIDUALS.NAME = 'John' | where HOUSEHOLDS.STATE = 'CO' and INDIVIDUALS.NAME = 'John' | ||
| + | </code> | ||
| 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. | 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. | ||
| Line 73: | Line 78: | ||
| In this example, the COUNT(*) does not require any additional indexes. | In this example, the COUNT(*) does not require any additional indexes. | ||
| + | <code sql> | ||
| select count(*) | select count(*) | ||
| from INDIVIDUALS | from INDIVIDUALS | ||
| where STATE = 'CO' and NAME = 'John' | where STATE = 'CO' and NAME = 'John' | ||
| + | </code> | ||
| The STATE and NAME indexes which were created to satisfy the criteria will also satisfy the requested count. | The STATE and NAME indexes which were created to satisfy the criteria will also satisfy the requested count. | ||
| Line 93: | Line 100: | ||
| In this example, the ORDER BY clause is optimized using the same index used for processing criteria. | In this example, the ORDER BY clause is optimized using the same index used for processing criteria. | ||
| + | <code sql> | ||
| select NAME, ADDRESS1, ADDRESS2, PHONE | select NAME, ADDRESS1, ADDRESS2, PHONE | ||
| from INDIVIDUALS | from INDIVIDUALS | ||
| where STATE in ('CA','CO') and NAME = 'John' | where STATE in ('CA','CO') and NAME = 'John' | ||
| order by STATE | order by STATE | ||
| + | </code> | ||
| The STATE index will be used to satisfy both the criteria and the ORDER BY clause. | The STATE index will be used to satisfy both the criteria and the ORDER BY clause. | ||