My SQL Dump

MySQL musings by a self professed MySQL Geek

Previous Entry Share Next Entry
How to support COUNT(DISTINCT expression) expressions with Flexviews.
swanhart
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, 
       (SELECT COUNT(*) 
          FROM child_materialization) d_alias
  FROM T1
 GROUP by a_alias, b_alias, c_alias;


This can easily be done manually with the current implementation, you just have to be careful to always refresh the child materialization first, before refreshing the dependent view.

I am seriously considering porting Flexviews directly into Drizzle. I'm excited about replication plugins as this may make it easy to produce the necessary table change logs to support the materialization logic. Drizzle is becoming completely plugin oriented, so eventually materialized view rewrite and other cool features could be implemented too as optimizer plugins.

Here is a response I wrote up from our twitter exchange:
http://krow.livejournal.com/641510.html

"Here’s a related item from Justin Swanhart: how to support COUNT(DISTINCT expression) expressions with Flexviews. [...]"

Log Buffer #154

Thanks for the mention in Log Buffer.

I'm looking forward to porting Flexviews into Drizzle.

Кстати, swanhart, скоро выйдет фильм Форсаж 5, советую посмотреть.

You are viewing swanhart