In MySQL typically the only way to maintain aggregate tables is with CREATE TABLE AS ...
While this makes creating the aggregate tables convenient, this requires computing the entire aggregate table each time it must be refreshed. This a very expensive process and if you have many aggregate tables (also known as materialized views) it can take a very long time to generate some of these tables.
What makes Flexviews different?
Flexviews takes a different approach to creating and maintaining those aggregate tables. It records changes to base tables with triggers. This allows it to incrementally update the materialized view after it's initial computation. The aggregate table is created and maintained by a group of stored procedures.
What is the status of the project?
Flexviews is still a work in progress. It requires some trial and error to get used to how to define the aggregate tables, and what functions can be used. Flexviews has a few restrictions:
* Only equijoins between tables. No outer joins.
* Only COUNT(),SUM(),AVG() are supported. No DISTINCT or AGG(DISTINCT).
* You have a dedicated slave to compute the views on.
* You are using MySQL 5.0 or greater
In general if your queries fit the form:
| SELECT | t1.c1,t2.c2,t2.c3,SUM(t1.c4 + t1.c7),COUNT(t1.c5),AVG(t2.c6) |
| FROM | table1 t1 |
| JOIN | table2 t2 on t1.c1 = t2.c2 |
| GROUP BY | t1.c1,t2.c2,t2.c3 |
What if I need help?
Post a message in the sourceforge project support forum and I will get right back to you with the answer. If you find bugs or problems there is a tracker to report those too.
There is a sample database included in the tarball with instructions on how to install and test out the package. I suggest using MySQL sandbox to try it out.
About the project
I started this project in my free time while working for AdBrite. Once the initial proof of concept was done, I got the go ahead to continue working on the project at work, and I also secured a promise to release the source once the project was completed. When I left AdBrite authorized the source release. Since then I've continued development.
About the author...
I am currently employed by Kickfire, working on an exciting new database appliance for MySQL which enables amazing performance for data mart and OLAP applications - without aggregate tables. Previously I've been employed as a database architect at AdBrite, as a DBA at Yahoo! and at Easynews.com. I've been working on production database applications for nearly ten years, as well as desiging interesting applications like the multimedia thumbnails and search engine at Easynews, and a product configuration engine for Pride Mobility Products Corp.
-->