This is an old revision of the document!


Administration: Indexing Strategies

Indexing Concepts

Aggregations

Indexes are most commonly used for processing criteria and joining tables, but they can also be quite helpful for aggregations. 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.

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.

As was seen with criteria and table joins, sometimes an index is not the fastest approach to aggregating data. Using an index for aggregations requires scanning the index from the beginning to the end. If the criteria isolates only a small number of rows, it would be faster to simply retrieve the rows and aggregate them directly, than to scan millions of entries in an index.

As a general rule of thumb, it is valuable to use indexing for processing criteria when aggregating greater than 0.01% of the rows in a table. If aggregating less than 0.01% of the rows, the overhead of indexes exceeds its gains, and it is better to simply retrieve the data from the database.

Omnidex automatically considers this issue when optimizing SQL queries. Omnidex maintains a configuration setting called the AGGREGATION_THRESHOLD which controls whether to use an indexes to resolve aggregations. The AGGREGATION_THRESHOLD defaults to 0.01%, meaning that indexes will be used to aggregate more than 0.01% of the data. This setting can be overridden as needed.

The following article explains basic strategies for optimizing aggregations.

Additional Resources

See also:

 
Back to top
admin/indexing/concepts/aggregations.1295302697.txt.gz ยท Last modified: 2016/06/28 22:38 (external edit)