Abstract

Aggregate functions calculations could be effectively optimized by caching the values inside storage engine indexes.

Known Solutions

Some competitor products already do this:

• IBM DB2 supports generic syntax, but only supported by EVI index:
  https://webcache.googleusercontent.com/search?q=cache:C3dZy_OTpX0J:https://www.ibm.com/developerworks/ibmi/library/i-accelerated-analytics-db2-evi-tech/index.html+&cd=1&hl=en&ct=clnk&gl=fi&lr=lang_en%7Clang_ru
  the article also tells that MQT index supports a list of aggregate functions
• DexterityDB supports MIN, MAX, SUM, AVG optimizations.
• Mariadb has min/max https://mariadb.com/kb/en/library/minmax-optimization/

• marko mentioned https://www.postgresql.org/docs/9.5/gist-intro.html but I doubt it's there.

Implementation

The known algorithmic approach is to construct an interval tree.
It can be done by augmenting Innodb B-tree index.
The constraints for aggregate function are the same as for window functions, i.e. it should follow monoid requirements.