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?
Unimin Corporation - Spruce Pine