Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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).

share|improve this question
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? –  Nick Chammas Sep 22 '11 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 '11 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. –  Nick Chammas Sep 22 '11 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 '11 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. –  Nick Chammas Sep 22 '11 at 17:59
add comment

1 Answer

up vote 1 down vote accepted

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

share|improve this answer
    
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 '11 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 '11 at 18:43
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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