Sunday, 25 January 2009

How to create an efficient downloads tracking table in MySql?

At the moment I have a "tracking" table, which I use to store both searches and downloads.

TABLE `tracking`
`TID` bigint(20) NOT NULL auto_increment,
`PadID` bigint(20) NOT NULL,
`Phrase` text NOT NULL,
`PageHandle` varchar(10) NOT NULL,
`Referer` text NOT NULL,
`Agent` text NOT NULL,
`Lang` text NOT NULL,
`RemAddress` text NOT NULL,
`DateAdded` timestamp NOT NULL default CURRENT_TIMESTAMP,
PRIMARY KEY  (`TID`)

I think i'm going to just use this table for search tracking and create a new table for tracking downloads, this isn't really a problem as in most cases (apart from basic info like IP and useragent etc) it uses differnet fields.

I'm going to work on the principle that one download equals for unique IP & UserAgent & day. In English one download per user agent from a specific IP address per day.

The obvious thing to do would be to up the IP address, user agent and the day on one field and make it a unique index, but thats going to make the field quite big. Maybe the user agent string isn't important? and I could just use IP and Day, with the day in Julian format.

Whats the table look like that you use Mike?

Comments?

No comments:

Post a Comment