Harold,
Yes that information is available dynamically as you described. However, I
can think of at least two situations where what he wants to do is not only
useful but an excellent optimization.
First, he could be calculating some kind of "static" reporting. These are
reports that are generally summaries of activities over a period of time.
Since there is no future change to the data (like a daily sales report,
for example) computing those numbers once saves you LOTS of time that you
can be using for other, more dynamic queries.
This would also the first step in creating your own OLAP cube. For each
statistic, you save yourself a lot of time if you compute the results of
several GROUP BY functions (SUM, AVG, STD, etc) and store those results
into intermediate tables. Then when you want to start slicing and dicing
your data, you do more retrieval and less computations. This seriously
improves the performance of your reports. Each new record inserted into
your cube would require you to update every computed statistics table that
covered your new record. But that *is* the trade off, isn't it? Much
faster analysis for much slower transaction performance. Thus the basic
difference between OLAP and OLTP.
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
news <[EMAIL PROTECTED]> wrote on 10/12/2004 05:05:12 AM:
> In article <[EMAIL PROTECTED]>,
> Laszlo Thoth <[EMAIL PROTECTED]> writes:
>
> > I'm trying to create a single UPDATE query to deal with the
> following problem:
> > ==================================================================
> > -- I've got two tables:
>
> > CREATE TABLE `banannas` (
> > `owner` varchar(15) NOT NULL default ''
> > );
>
> > CREATE TABLE `monkeys` (
> > `name` varchar(15) default NULL,
> > `banannacount` int(4) default NULL
> > );
>
> > -- I've got three monkeys:
>
> > INSERT INTO `monkeys` VALUES
('bonzo',NULL),('dunston',NULL),('ham',NULL);
>
> > -- Some of those monkeys have banannas.
> > -- Some of those monkeys have more than one bananna.
> > -- Some of those monkeys don't have any banannas.
>
> > INSERT INTO `banannas` VALUES ('bonzo'),('bonzo'),('bonzo'),('ham');
> > ==================================================================
>
> > I'm trying to write an UPDATE query so that monkeys.banannacount
> is set to the
> > number of banannas each monkey owns.
>
> Why would you want to do that? bananacount is something you can
> calculate with a LEFT JOIN and a GROUP BY, so storing it in the DB
> would break normalization.
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
>
