Re: how many record versions

On Sun, 23 May 2004 23:32:48 +0700, David Garamond
<lists(at)zara(dot)6(dot)isreserved(dot)com> wrote:
>Actually, each record will be incremented probably only thousands of
>times a day. But there are many banners. Each record has a (bannerid,
>campaignid, websiteid, date, countrycode) "dimensions" and (impression,
>click) "measures".

If you need all of bannerid, campaignid, websiteid, date, countrycode to
identify a row, it may be worth the effort to split this up into two
tables:

CREATE TABLE dimensions (
dimensionid int PRIMARY KEY,
bannerid ...,
campaignid ...,
websiteid ...,
date ...,
countrycode ...,
UNIQUE (bannerid, ..., countrycode)
);

CREATE TABLE measures (
dimensionid int PRIMARY KEY REFERENCES dimensions,
impression ...,
click ...
);

Thus you'd only update measures thousands of times and the index would
be much more compact, because the PK is only a four byte integer.

> The table currently has +- 1,5-2 mil records (it's in
>MyISAM MySQL), so I'm not sure if I can use that many sequences which
>Tom suggested. Every impression (banner view) and click will result in a
>SQL statement

Schedule a
VACUUM ANALYSE measures;
for every 100000 updates or so.

>I'm contemplating of moving to Postgres, but am worried with the MVCC
>thing. I've previously tried briefly using InnoDB in MySQL but have to
>revert back to MyISAM because the load increased significantly.

You mean InnoDB cannot handle the load?

Servus
Manfred