Unlike COUNT(expression), COUNT(DISTINCT expression) is not a distributable function. That is, the expression can not be computed by looking at only the changed rows in the table change logs.



The following view can not be FAST refreshed with Flexviews today:

SELECT a a_alias, 
       b b_alias, 
       c c_alias, 
       COUNT(DISTINCT d) d_alias
  FROM T1
 GROUP BY a, b, c;

However, a dependent child materialization could be created to support the value for COUNT(DISTINCT d):

-- child materialization, dependent subview
--There will be one row for each DISTINCT value of d

SELECT a a_alias, 
       b b_alias, 
       c c_alias, 
       count(*) d_alias_cnt
  FROM T1
 GROUP BY a, b, c, d;

The original view could then be rewritten as:

SELECT a a_alias, 
       b b_alias, 
       c c_alias, …

At today's keynote by Mark Callaghan one of the new options he talked about are table change logs. He mentioned they might be of use to external applications, like Flexviews, which he mentioned but not directly. He asked if the guy who wrote it was in the audience, so I got to wave my hand and yell 'Flexviews!'.

I caught up with Mark at the Facebook party this evening. I had a chance to talk to him not only about the change logs, but also about Kickfire and the SQL chip. He asked me what I thought about working at Kickfire and I smiled and said I love it. I think I said "I've never been able to join a billion row table to a hundred million row table, sort, group and get results back in less than a minute" and I'm sure the smile never left my face.

As far as the table change logs, he verified:

• 
  
• The global transaction id will be stored in the table
  
• OLD and NEW …

Make sure you set sp_max_recursion_depth - see the instructions file.

changes:
functions/procedures now take parameters in DB,TABLE order.
demo database removed from sourceforge and added into SVN/release tarball.
GRANT USAGE on *.* to flexviews; is now the default permission level of the flexviews user
I removed the mview_ prefix from filenames.
flexviews.add_table() now checks for the existence of the table and the table's mvlog

get it at http://flexviews.sourceforge.net

I will be demonstrating the features of Flexviews as well as talking about how you can improve performance by:

• 
  
• pre-aggregating important tables for improved query performance
  
• pre-calculating joins on InnoDB using materialized views
  
• nesting incrementally refreshed materialized views to further improve performance
  

http://mysql.meetup.com/101/

available now: Flexviews for MySQL 1.0.2-alpha

bug fixes:
ensure_validity() was failing when an incremental refresh mview did not contain an AVG()
the delta application phase of refresh was not cleaning up the delta log
removed serialization overhead, improving dml performance. needs further improvement.

Get it at:
http://sourceforge.net/projects/flexviews/

I keep talking about Flexviews, but I figured I'd step back and explain exactly what it does and how it can help you maintain aggregate tables cheaply. Read that again. It really is important. If you have a BI or DW running MySQL then you probably spend a lot of your time creating aggregate tables with cron jobs or some other ETL tool.

These aggregate generating queries might take many hours to run, and running multiple aggregations concurrently likely severely impacts performance. The more data you have the longer the aggregations take to run. If you are attempting to answer real-time business questions this presents a big challenge. Flexviews to the rescue. It gets rid of those cron jobs and custom aggregation scripts and replaces them with an API for creating materialized views (a fancy name for those aggregate tables). It supports two kinds of views: COMPLETE and INCREMENTAL refresh.

A COMPLETE materialized …

I've made a few fixes to the SVN version of Flexviews:
flexviews.enable() would return an error when trying to create an incremental refresh materialized view without an AVG() aggregate expression in the select clause.

Performance of DML is now improved substantially. Flexviews is designed to work in an environment with serialized DML (such as a MySQL slave). I removed extra serialization enforcement overhead. Previously two tables were involved in coordinating the 'uow_id' generation to ensure serialization in the logs. Now only one table is utilized. I need to add a procedure to auto-cleanup this new table.


There and there are a lot more to make. On my list of things to do:
1) code cleanup
flexviews went through several iterations and there are still 'mview' references all over the
place

some of the code is encased in comments because it was extracted by …

There were a number of problems in the installer script and in support for COMPLETE refresh materialized views. These problems have been rectified and an updated version is available on sourceforge. Also, you can pull the latest source from the svn repository:
https://flexviews.svn.sourceforge.net/svnroot/flexviews/src

Here is a little example for complete refresh:

CALL flexviews.create('mv2', 'test', 'COMPLETE', 1);
SET @MVID = LAST_INSERT_ID();
CALL flexviews.set_definition(@MVID, 'SELECT 1');
CALL flexviews.enable(@MVID);
CALL flexviews.refresh(@MVID, 'COMPLETE');

I am proud to announce the availability of Flexviews for MySQL 1.0.0-alpha under LGPLv3!
http://flexviews.sourceforge.net

The documentation needs quite a bit of work. When you run into problems (and I'm sure you will) you can ask for help on the sourceforge support tracker. Please download the example database for some examples of incremental refreshable views.

I haven't tested COMPLETE refresh views in quite a while, but I think they break when you try to enable them. I'll fix this today or tomorrow and upload a new version. INCREMENTAL refresh is what is interesting anyway :)

Please report bugs (of which I'm sure there are quite a few), as well as feature requests, etc, on the sourceforge bug tracker.