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', pid tinyint(2) NOT NULL default '99', hits int(4) NOT NULL default '9999' ) 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. 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??? TIA Justin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
