List:General Discussion« Previous MessageNext Message »
From:SGreen Date:October 19 2005 1:29pm
Subject:Re: Input on Materialized Views
View as plain text  
Just like Jigal, I also had to lookup the term "materialized view". For 
the sake of others on the list trying to follow along: a materialized view 
is basically a self-updating snapshot of a table (or tables) usually 
containing some sort of intermediate statistical computations involving 

For instance, if you have the raw log of visitors to your web site stored 
in your database and you frequently ran queries that created summarations 
by day, you could save yourself a lot of processing time by periodically 
precomputing a "daily table" from your raw logs showing various statistics 
broken down for each date. Well, a materialized view would be one way to 
implement that "daily table".

Jigal van Hemert <jigal@stripped> wrote on 10/19/2005 03:51:26 AM:

> Andrew Roth wrote:
> > Hi all,
> > 
> > We are a group of three students in Professor Ric. Holt's Software
> > Architecture class at the University of Waterloo.  As our project, we
> > would like to examine the MySQL source to determine the best way to
> > implement materlialized views.
> It would be wise to hang around and see if someone from MySQL AB has 
> time to answer the questions, but here's my personal view...
> I had to look up materialized views and if I read it correctly what is 
> said about this at 
> > 1. How feasible would implementing materialized views be?
> It would be quite an adventure, but they might be useful in certain 
> situations (low concurrency, but need for more speed or higher 
> concurrency and less need for accuracy).

The feasability is directly proportional to the sum of your ambition and 
talent. It is probable that MySQL will have this feature at some point. 
You are in the position to make it happen sooner rather than later.

> > 2. Any reasons why materialized views haven't been added already?
> Since 'views' are only introduced in MySQL 5.0 I guess that:
> - there has not been time yet to think about materialized views
> - no programmer has had the time to build it
> - no-one has sponsored to hire extra programmers to build it

(To echo Jigal): Views and triggers are new to v5.0. They are still 
massaging the last few kinks out of the basic behaviors. Tying them 
together to form a "materialized view" is not on the workplan (yet).

> 3. Any web pages or archived messages relating to this.  (I have
> search the archives and didn't find anything.)

Nope, too new. You are the first.

> > 4. Any comments at all relating to this endeavor.
> Good luck? ;-)
> I think that you have to make changes in a lot of areas. At least you 
> will have to think of:
> - storage engine(s) (updating materialized views on commit)
> - query optimizer (trying to rewrite the query to use MVs instead)
> - adding handling of the new keywords
> - sceduling of refreshes during off-peak time
> Kind regards, Jigal.

Good Luck! You are quickly going to exceed the general level of knowledge 
supported on this list. If you start running into specific problems, you 
may want to shift this thread to the INTERNALS list, instead. Please keep 
us informed of your progres, OK?

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine

Input on Materialized ViewsAndrew Roth19 Oct
  • Re: Input on Materialized ViewsJigal van Hemert19 Oct
    • Re: Input on Materialized ViewsSGreen19 Oct
      • Re: Input on Materialized ViewsAndrew Roth19 Oct
        • Re: Input on Materialized ViewsSGreen19 Oct
          • Re: Input on Materialized ViewsAndrew Roth20 Oct
Re: Input on Materialized ViewsHeikki Tuuri20 Oct
  • Re: Input on Materialized ViewsPooly21 Oct
RE: Input on Materialized ViewsJon Frisby21 Oct