This shows you the differences between two versions of the page.
|
admin:indexing:strategies:joins [2011/03/02 18:37] 127.0.0.1 external edit |
admin:indexing:strategies:joins [2011/03/17 21:43] deb |
||
|---|---|---|---|
| Line 1: | Line 1: | ||
| ~~NOTOC~~ | ~~NOTOC~~ | ||
| + | |||
| {{page>:top_add&nofooter&noeditbtn}} | {{page>:top_add&nofooter&noeditbtn}} | ||
| Line 16: | Line 17: | ||
| ==== Optimizing Table Joins ==== | ==== Optimizing Table Joins ==== | ||
| - | Tables joins are usually optimized by creating indexes on the table constraints. For relational databases, it is only necessary to index the foreign keys since the relational database's indexes on the primary keys will suffice. For raw data files, both the primary and foreign keys should be indexed. | + | Table joins are usually optimized by creating indexes on the table constraints. For relational databases, it is only necessary to index the foreign keys since the relational database's indexes on the primary keys will suffice. For raw data files, both the primary and foreign keys should be indexed. |
| Multi-column constraints should be indexed using multi-column Omnidex indexes. These are custom indexes that combine multiple columns into an index. The order of the columns in the indexes should match the order of the columns in the constraint. | Multi-column constraints should be indexed using multi-column Omnidex indexes. These are custom indexes that combine multiple columns into an index. The order of the columns in the indexes should match the order of the columns in the constraint. | ||
| Line 29: | Line 30: | ||
| <code sql> | <code sql> | ||
| + | |||
| select I.NAME, H.ADDRESS, H.CITY, H.STATE, H.ZIP, I.PHONE | select I.NAME, H.ADDRESS, H.CITY, H.STATE, H.ZIP, I.PHONE | ||
| from INDIVIDUALS I join HOUSEHOLDS H on I.HOUSEHOLD = H.HOUSEHOLD | from INDIVIDUALS I join HOUSEHOLDS H on I.HOUSEHOLD = H.HOUSEHOLD | ||
| where H.STATE = 'CO' and I.NAME = 'John'; | where H.STATE = 'CO' and I.NAME = 'John'; | ||
| </code> | </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 41: | Line 44: | ||
| <code sql> | <code sql> | ||
| + | |||
| select H.ADDRESS, H.CITY, H.STATE, H.ZIP | select H.ADDRESS, H.CITY, H.STATE, H.ZIP | ||
| from HOUSEHOLDS H join COUNTIES C on H.STATE = C.STATE and | from HOUSEHOLDS H join COUNTIES C on H.STATE = C.STATE and | ||
| Line 46: | Line 50: | ||
| where C.STATE = 'CO' and C.COUNTY = 'Boulder'; | where C.STATE = 'CO' and C.COUNTY = 'Boulder'; | ||
| </code> | </code> | ||
| + | |||
| For relational databases, the STATE and COUNTY_CODE columns of HOUSEHOLD should be indexed using a multi-column Omnidex index. For raw data files, the STATE and COUNTY_CODE columns of COUNTIES should also be a multi-column Omnidex index. For all databases, the COUNTY column of COUNTIES should be a QuickText index. | For relational databases, the STATE and COUNTY_CODE columns of HOUSEHOLD should be indexed using a multi-column Omnidex index. For raw data files, the STATE and COUNTY_CODE columns of COUNTIES should also be a multi-column Omnidex index. For all databases, the COUNTY column of COUNTIES should be a QuickText index. | ||