This shows you the differences between two versions of the page.
| Both sides previous revision Previous revision Next revision | Previous revision | ||
|
admin:indexing:concepts:aggregations [2011/01/18 05:28] els |
admin:indexing:concepts:aggregations [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: Omnidex Indexing ====== |
| ===== Indexing Concepts ===== | ===== Indexing Concepts ===== | ||
| Line 23: | Line 25: | ||
| The following query requests a simple count from a table. | The following query requests a simple count from a table. | ||
| + | |||
| + | <code> | ||
| select count(*) | select count(*) | ||
| Line 29: | Line 33: | ||
| CITY = 'Boulder' and | CITY = 'Boulder' and | ||
| ZIP = '80301'; | ZIP = '80301'; | ||
| + | </code> | ||
| + | |||
| Omnidex will search its indexes to process the criteria, and it will have the count ready when it is complete. There is no additional work needed to return the count, and no access to the database is required. Since these types of queries are very common, this makes Omnidex especially fast as a count engine. | Omnidex will search its indexes to process the criteria, and it will have the count ready when it is complete. There is no additional work needed to return the count, and no access to the database is required. Since these types of queries are very common, this makes Omnidex especially fast as a count engine. | ||
| Line 35: | Line 41: | ||
| Queries that aggregate data often use GROUP BY clauses or other aggregation functions. These queries can be optimized with Omnidex indexing as well. The following query shows counts from the HOUSEHOLDS table aggregated by STATE, CITY and ZIP. | Queries that aggregate data often use GROUP BY clauses or other aggregation functions. These queries can be optimized with Omnidex indexing as well. The following query shows counts from the HOUSEHOLDS table aggregated by STATE, CITY and ZIP. | ||
| + | |||
| + | <code> | ||
| select STATE, CITY, ZIP, count(*) | select STATE, CITY, ZIP, count(*) | ||
| from HOUSEHOLDS | from HOUSEHOLDS | ||
| group by STATE, CITY, ZIP | group by STATE, CITY, ZIP | ||
| + | </code> | ||
| - | An index has the advantage of already being in sorted order. If a multi-column index contains all of the columns in the GROUP BY clause, as well as all of the columns referenced in COUNT, SUM, AVERAGE, MIN and MAX functions, the the index can simply be scanned in sorted order, and the aggregations can be returned without accessing the database. Therefore, a multi-column index containing STATE, CITY and ZIP would allow this query to be optimized without accessing the database. | + | An index has the advantage of already being in sorted order. If the STATE, CITY and ZIP columns are all individually indexed with Omnidex, then these three indexes will be used in combination to perform the aggregation. The STATE index will be scanned, followed by the CITY index, followed by the ZIP index. The aggregations can be returned without accessing the database. |
| - | This technique can be used even after other indexes have been used to process criteria. After the criteria has been processed, the isolated record pointers can be used as a filter when the aggregation index is scanned. This allows for great flexibility, since the columns referenced in criteria do not need to be included in the aggregation index. In the following query, the same aggregations are restricted to people in the United States. | + | Some aggregations benefit from multi-column indexes, such as a single index with the STATE, CITY and ZIP. This is more efficient than using three separate indexes, and is especially important if any of the columns contain fairly unique values. It is also required for aggregations that use the SUM, AVERAGE, MIN and MAX functions. |
| + | |||
| + | These techniques can be used even after other indexes have been used to process criteria. After the criteria has been processed, the isolated record pointers can be used as a filter when the aggregation index is scanned. This allows for great flexibility, since the columns referenced in criteria do not need to be included in the aggregation index. In the following query, the same aggregations are restricted to people in the United States. | ||
| + | |||
| + | <code> | ||
| select STATE, CITY, ZIP, count(*) | select STATE, CITY, ZIP, count(*) | ||
| Line 48: | Line 61: | ||
| where COUNTRY = 'US' | where COUNTRY = 'US' | ||
| group by STATE, CITY, ZIP | group by STATE, CITY, ZIP | ||
| + | </code> | ||
| + | |||
| Omnidex will first qualify the rows in the United States, and will then allow those record pointers to be used as a filter against the aggregation index. | Omnidex will first qualify the rows in the United States, and will then allow those record pointers to be used as a filter against the aggregation index. | ||
| Line 58: | Line 73: | ||
| ==== ==== | ==== ==== | ||
| - | The [[admin:indexing:basic:aggregations|following article]] explains basic strategies for optimizing aggregations. | + | The [[admin:indexing:strategies:aggregations|following article]] explains basic strategies for optimizing aggregations. |
| ===== ===== | ===== ===== | ||