Re: how many record versions

On Sun, 23 May 2004 23:32:48 +0700, David Garamond
<lists@zara.6.isreserved.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