1

I'm looking for a tool that will let me precompute expensive aggregations some time after rows have been inserted into a MySQL database. For example, if I often run a SUM() on a column, I'd like to instead store a running sum in a separate table that can be queried instead of the raw data.

My use case is for heavily-read live stat data (graphs, etc.).

Are there more extensive options beyond materialized views? The thought behind finding a system like this is to reduce the amount of raw sql required to create materialized views as the users of the system would like to specify aggregations without SQL (as new aggregations and stats are being created constantly).

5
  • 2
    Well, the way a database would precompute expensive aggregations would be to do exactly that: Create a materialized view that captures those aggregations. Are you looking for an automated way to generate the materialized views that satisfy your most frequent aggregation queries? Sep 22, 2011 at 5:37
  • Are you saying the database creates its own materialized views? If not, I'm trying to find a tool that lets us create these types of views based on what aggregations users want to cache based on current and future tables, columns, and desired metrics, without them having to spend time writing SQL.
    – Max
    Sep 22, 2011 at 15:42
  • Designing the appropriate materialized views is your job as the database designer or administrator, and you will be best equipped to do that if you know what kinds of aggregations are being performed against your database. I am not aware of a tool that can look at a database's workload and generate the appropriate materialized views, but I guess in theory a tool could do that. If the primary use of your database is for analysis on large data sets, an RDBMS may be the wrong tool for you and you may need something like an OLAP cube. Sep 22, 2011 at 15:50
  • The tool does not figure out what aggregations need to be done itself, it's simply an easier interface to say "precompute, or accumulate this sum() on this column" for the users who are stat analysts not dbas, rather than having to create a view. I have looked into OLAP cubes, but it seems they are mainly used for loading a large data set for a single analysis session, rather than for processing real-time data, correct?
    – Max
    Sep 22, 2011 at 15:59
  • You would periodically refresh your cube based on your reporting needs; I don't know enough about cubes to say if they can be effectively used to process data real0-time, but I know cubes are flexible in how they let you slice and dice your data. I you want to do this in a traditional RDBMS, you'll have to look at the most common or expensive aggregations you see being run against your database and design the appropriate materialized views yourself. Sep 22, 2011 at 17:59

1 Answer 1

1

Another option would be to put the data into an OLAP cube. OLAP cubes are designed for doing aggregates.

2
  • Thanks. I asked a commenter above if OLAP cubes would handle real-time storage and computation of aggregations, or if it's meant for a user to load a large data set to analyze static data in a single sitting.
    – Max
    Sep 22, 2011 at 17:41
  • That depends on the storage model. There is ROLAP which is real time and stores some data in the cube and gets some from the source database so that the return from the cube is always real time. There's also MOLAP and HOLAP which go back to the source sometimes, or never.
    – mrdenny
    Sep 22, 2011 at 18:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.