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
