On 05-Apr-2003 Justin French wrote:
> Hi all,
>
> I'm currently logging page requests with the following table:
>
> CREATE TABLE counters_hits (
> pid tinyint(3) unsigned NOT NULL default '0',
> stamp varchar(30) NOT NULL default ''
> ) TYPE=MyISAM;
>
> In other words, for ever page hit on my site, I'm recording the unix
> timestamp & page ID.
>
> This equates to around 20bytes of data per row. On 50,000+ hits, this is
> turning into 2.4-ish meg of data.
>
>
> Now, I REALLY don't need to know the exact second of every hit on the
> site... no way!! I've decided counting hits on each pageID by the hour
> is
> detailed enough.
>
>
> CREATE TABLE test (
> year tinyint(4) NOT NULL default '127',
> month tinyint(2) NOT NULL default '12',
> day tinyint(2) NOT NULL default '31',
> hour tinyint(2) NOT NULL default '24',
yr YEAR(4) NOT NULL DEFAULT 0, // Yes. There is a 'YEAR' column type
mo TINYINT(2) NOT NULL DEFAULT 0, // Change names to avoid possible
dy TINYINT(2) NOT NULL DEFAULT 0, // clash w/ Reserved words.
hr TINYINT(2) NOT NULL DEFAULT 24,
--- option 2 ---
hitdate DATE NOT NULL DEFAULT '0000-00-00', // Same size
hr TINYINT(2) NOT NULL DEFAULT 24, // as above
> pid tinyint(2) NOT NULL default '99',
> hits int(4) NOT NULL default '9999'
hits INT UNSIGNED NOT NULL DEFAULT 1 // can you have "negative" hits ?
> ) TYPE=MyISAM;
>
>
> I've entered in a few dummy rows, and they're 10 bytes each. By my sums,
> there'll be a maximum of:
>
> 24 rows a day x 10 bytes a row = 240 bytes a day
> 240 bytes a day x 365 days a year = 87600 bytes a year (87.6k)
>
>
> So, by moving from timestamps to the above format, and throwing away the
> accuracy of seconds and minutes, I'm going to have a maximum of 87k a
> year
> of data, compared to my current situation of 2.4 meg of data in a few
> months.
Looking at your current table (above) varchar(30) is _not_ a timestamp.
It's 1 byte of pid and (a possible) 30+1 bytes of characters.
My suggested changes is 4 bytes --but a native TIMESTAMP is 4 bytes also.
Plus you don't have explicitly set it.
On a high volume ( > 2 million hits/day) ad site, I use:
CREATE TABLE hit (
hittime timestamp(14) NOT NULL,
inet int(10) unsigned NOT NULL default '0',
wmid smallint(5) unsigned NOT NULL default '0',
site char(2) NOT NULL default 'xx',
uid bigint(20) unsigned NOT NULL default '0',
urlid bigint(20) unsigned NOT NULL default '0',
KEY idx_i (inet)
);
At the end of day, prior days data is sumarised into a 'history' table;
then the 'hit' table is reset.
>
>
> Can someone confirm if my maths are all good, and possibly advise any
> areas
> where I might further optimise this?
>
>
> The only other thing I'm worried about is the fact that I'll have to do a
> couple of queries PER HIT using this system (check if the row exists,
> then
> either add one hit or insert a new row), rather than just a simple
> INSERT...
> Any ideas on this too???
>
Record all hits, one record/hit and then use COUNT(*) ... GROUP BY
to get your totals.
Regards,
--
Don Read [EMAIL PROTECTED]
-- It's always darkest before the dawn. So if you are going to
steal the neighbor's newspaper, that's the time to do it.
(53kr33t w0rdz: sql table query)
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
